Update script for updating a table field in SQL Server from NULL to non-NULL

Question

I have a field (say, foo) in a table in a SQL Server database that was originally defined as nullable, but new requirements indicate that this field must be non-null.

What's the best way of updating this field to non-null via an update script without deleting the contents of the table? I tried generating a script from the Design view, but fails during execution because the current contents of the table had NULL values for foo. Worse yet, if I ignored this error, it proceeds to delete all the contents of the table!

Answer 1

You can set a default value to the not-nullable field. If you want to change it from nullable to not-nullable you must provide some value that can be assigned to rows with null value in that field.

Answer 2

You've got to set a value to any rows with NULL in the NULLable column before you change it to NOT NULL.

-- Clean up the data which won't comply with the schema changes
UPDATE t SET foo = 0 WHERE foo IS NULL

-- Apply the NOT NULL
ALTER TABLE t ALTER COLUMN foo int NOT NULL

-- Add a default for the future if that's what you want
ALTER TABLE t ADD CONSTRAINT t_foo_def DEFAULT 0 FOR foo