How to change a column from NULL to NOT NULL in SQL Server?

MS SQL Server - How to change an existing column from NULL to NOT NULL?

You have an existing table with one nullable column, now you want to change that column to not null.

You have to take two steps:

1. Update the table to delete all NULL values:

UPDATE table_name 
SET col_name = 0
WHERE col_name IS NULL;

2. Alter the table and change the column to not nullable:

ALTER TABLE table_name
ALTER COLUMN col_name data_type NOT NULL;

If you also want to add a default value to that column, run this query after two steps above:

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name DEFAULT default_value FOR col_name;

---

Need a good GUI Tool for MS SQL Server? Try TablePlus, a modern, native tool for multiple relational databases. It’s free anyway.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS