CODEWITHSUNDEEP
sqldatabasesql-server-2008
Joy1979
Joy1979

Reputation: 609

Cannot insert a new column in a table

I have a Product table in which I want to create a new columns Modified_By and Modified_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"

Upvotes: 4

Views: 12339

Answers (2)

GeorgesD
GeorgesD

Reputation: 1082

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.

Upvotes: 6

Ciarán
Ciarán

Reputation: 3057

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;

Upvotes: 1

Related Questions