Cannot insert a new column in a table
Solution 1
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
You have to set a default value.
ALTER TABLE Product ADD Modified_By datetime DEFAULT (GETDATE())
The default value will be set to today.
Solution 2
I find the interactive design is not very good at this sort of thing. It's better to simply add the constraint manually
ALTER TABLE Table_Name ALTER COLUMN Column_Name DataType NOT NULL
E.g.
ALTER TABLE MyTable ALTER COLUMN A_Column Int NOT NULL;
Related videos on Youtube
Joy1979
Updated on September 14, 2022Comments
-
Joy1979 over 1 year
I have a
Product
table in which I want to create a new columnsModified_By
andModified_Date_Time
. These columns do not allow nulls.However, as the database already has data, in order to create this column, I had to defined as "allowing nulls". Then, I run a process which updated the new column. The last step was to uncheck the "Allow nulls" property, but when I tried to save the table changes, I got the following error:
'Product_Details' table - Unable to modify table.
Cannot insert the value NULL into column 'Modified_Date_Time', table 'Vendor Products.dbo.Tmp_Product_Details'; column does not allow nulls. INSERT fails. The statement has been terminated.All the rows were succesfully updated with the correct value in the "Modified_By" and "Modified_Date_Time" column, so I don't know why I get this error...Anyway, it seems like a new "temporary" table was created by SQL Server 2008, because I don't have any table with the name "Tmp_Orders"
-
Anthony Grist over 11 yearsYou don't have to set a default value; allowing NULLs, updating the data so there aren't any, then disallowing NULLs should work.
-
GeorgesD over 11 yearsYes,I agree, but it is simplier to do it with only one command. You always have more than one way to do things.
-
Anthony Grist over 11 yearsSure, if you want a default value on the column; there's no indication that they do, and saying that they have to set a default is totally wrong.
-
GeorgesD over 11 yearshe says "This columns does not allow nulls.". If you want to add a column that does not allow null then you have two ways: you create the column with a default value or you create a column that accept null you fill the column and you change the column property.
-
Joy1979 over 11 yearsHi, I have tried this query and result look like "Modified_By" column with "-1" as data in it. Please advise............Alter Table Product_Details Add Modified_By varchar (50) NOT NULL default -1
-
Joy1979 over 11 years@GeorgesD..Yes, I need column which does not allow nulls..Thx