Little SQL Server Tricks: Change Between NULL and NOT NULL Constraints

Changing between NULL and NOT NULL constraints for columns in SQL Server is simple. To turn a NOT NULL column into one that allows NULL, you can use this command:

1
ALTER TABLE [Table] ALTER COLUMN [Col] TYPE NULL

All you need to do is to replace [Table] with the name of your table, [Col] with the name of your column and TYPE with the datatype of the column. Execute the command and you are allowed to use NULL values for the specified column.

The other way from a nullable column to one that prevents NULL values needs a step more. First, you need to set a default value on rows currently not having a value:

1
2
3
UPDATE [Table]
   SET [Col] = 0 -- your new default value
WHERE [Col] IS NULL

When this default value is set, you can reuse the command from above but with NOT NULL:

1
ALTER TABLE [Table] ALTER COLUMN [Col] TYPE NOT NULL

That is all it takes to switch between NULL and NOT NULL. The challenge lies in finding a useful default value, which is a business decision you need to clear before you change the database structure.