Changing NULL to NOT NULL – Dealing with Data

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:

INSERT Simpletable VALUES (1, A, 1) , (2, B, 0) , (3, C, NULL) , (4, D, NULL) ;

Now I have 4 rows. If I run this:

ALTER TABLE dbo.SimpleTable ALTER COLUMN Status TINYINT NOT NULL;

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:

UPDATE dbo.SimpleTable SET Status = 0 WHERE status IS NULL;

Once I do that, I can easily run my ALTER and it succeeds.

 

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.