how to set column property NOT NULL in SQL Server

Question

I need to set my column property to NOT NULL, but I'm not able to set the property it produces the below error..

MY Query:

ALTER TABLE CartReceBenif
ALTER COLUMN RecipientId int NOT NULL;

enter image description here

ERROR

Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'RecipientId', table 'test.dbo.CartReceBenif'; column does not allow nulls. UPDATE fails. The statement has been terminated.

Can any one suggest what is the mistake..

You can't do that if you already have NULL values in that column. Is that the case? — shree.pat18
– shree.pat18, Commented Jun 24, 2014 at 5:56
You must use add constraints clause in order to add a constraints in your table. — Ankit Bajpai
– Ankit Bajpai, Commented Jun 24, 2014 at 5:56
make sure that your "RecipientId" column not exist with null record. possible then remove all record of table and apply ALTER Table query. — Rachit Patel
– Rachit Patel, Commented Jun 24, 2014 at 5:56
Add a default constraint for the same and check if it is containing some Null values or update the null values with any default value and check. — Azar
– Azar, Commented Jun 24, 2014 at 5:59
Thank-You.. @Rachit Patel and Azar , shree.part18.. my db had null value so it dosent work.. now it works fine.. — user3595072
– user3595072, Commented Jun 24, 2014 at 6:01

JRavil · Accepted Answer · 2014-06-24 06:07:53Z

3

Possible data present in this table and null values in this column. Try to update table, set values in this column to 1 for example before altering. Or just remove all data from the table before altering.

answered Jun 24, 2014 at 6:07

JRavil

485 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to set column property NOT NULL in SQL Server

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related