I have a existing table in which i am adding a column as NgoId whose datatype is int. I want to add as NOT NULL to the column .
When I'm adding this I am getting the following error:
Unable to modify table. Cannot insert the value NULL into column 'NgoId', table 'CSR.dbo.Tmp_tbl_post_category'; column does not allow nulls. INSERT fails. The statement has been terminated.
Please help how to add the column with NOT NULL.
As you already have data in the table, you cannot add a NOT NULL field, because the existing data will already violate the NOT NULL constraint.
You therefore have 2 choices:
a) Add the column with a DEFAULT value...
ALTER TABLE dbo.Tmp_tbl_post_category ADD NgoId INT DEFAULT 1;
...then run an update statement to correct the values to what they should actually be
b) Truncate the data from the table, add the column and then re-populate the table...
TRUNCATE TABLE dbo.Tmp_tbl_post_category;
ALTER TABLE dbo.Tmp_tbl_post_category ADD NgoId INT;
INSERT INTO dbo.Tmp_tbl_post_category...
