I wrote recently on how to alter a column in SQL Server from NULL to NOT NULL in a simple way. However I didn’t cover some of the cases where you have data in the column, or other restrictions that you might need to deal with. In this post, I want to look at a few options if you have data in the table.
Let’s take my SimpleTable and add data:
Now I have 4 rows. If I run this:
I get this error:
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column ‘Status’, table ‘Sandbox.dbo.SimpleTable’; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Essentially, SQL Server can’t mark this column as NOT NULL because there are NULLs in there. What can I do here?
UPDATE the Table
The only thing I can do is to change the data. I can run a query that removes all the NULLs in the column by setting them to some value. That’s what I’ll do here. I can choose a value here and run an UPDATE statement. Something like this:
Once I do that, I can easily run my ALTER and it succeeds.