Change a Nullable column to NOT NULL with Default Value

by

I think you will need to do this as three separate statements. I’ve been looking around and everything i’ve seen seems to suggest you can do it if you are adding a column, but not if you are altering one.

ALTER TABLE dbo.MyTable
ADD CONSTRAINT my_Con DEFAULT GETDATE() for created

UPDATE MyTable SET Created = GetDate() where Created IS NULL

ALTER TABLE dbo.MyTable 
ALTER COLUMN Created DATETIME NOT NULL

Leave a Comment