TSQL change column constraint between null and not null

Question

I am doing a exercise to change a column's constraint between null to not null. Table name: Production.categoriesTest. Please review my question and give me suggestion on what I did wrong and how to fix the issue. Thanks!

1. when table was created, the description column is set to NOT NULL
2. I used the update clause to set description column to NULL
3. When using alter table command to set the column description back to NOT NULL, I received an error message.

Error message:

Msg 515, Level 16, State 2, Line 98
Cannot insert the value NULL into column 'description', table 'TSQL2012.Production.categoriesTest'; column does not allow nulls. UPDATE fails.

Create table command:

 create table Production.categoriesTest
 categoryid INT not null identity,
 categoryname nvarchar(15) not null,
 description nvarchar(200) not null,
 constraint PK_Categories1 Primary Key (categoryid)
--update clause make all description column becomes 'NULL"
 update Production.categoriesTest
 set description = NULL
 where categoryid = 8;

The description column became 'NULL'

I did insert a value for description column in the able after the column set to Null.

• Steve Pettifer: This doesn't make a lot of sense as written because you have created a column declared as NOT NULL and immediately try to update the same field to NULL. Of course it fails. What you haven't done is to is to issue an ALTER TABLE command like so: ALTER TABLE Production.categoriesTest ALTER COLUMN description NVARCHAR(200) NULL to make the column NULLable. An UPDATE statement cannot alter the definition, only the data and it is bound by the constraint.

Answer 1

When trying to return the column back to NOT NULL SQL Server immediately checks the constraint, which fails because the rows for that column were set to NULL.

For an existing column, you have to update the table to remove any NULL values before you can set it as NOT NULL.

• user2061466: Thanks for the reply AlwaysLoadingData and Steve. I used update clause to set each row to a value that is not null. I was able to update the column constraint back to 'NOT NULL'