1

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.

6

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...

Not the answer you're looking for? Browse other questions tagged or ask your own question.