SQL Server - Change a Nullable column to NOT NULL with Default Value

Question

Hey everyone,

I came across an old table today with a datetime column called 'Created' which allows nulls. Now, I'd want to change this so that it is NOT NULL, and also include a constraint to add in a default value (getdate()).

So far I've got the following script, which works fine provided that i've cleaned up all the nulls beforehand:

ALTER TABLE dbo.MyTable ALTER COLUMN Created DATETIME NOT NULL 

Is there any way to also specify the default value as well on the ALTER statement?

Answer 1

If its SQL Server you can do it on the column properties within design view

Try this?:

ALTER TABLE dbo.TableName ADD CONSTRAINT DF_TableName_ColumnName DEFAULT '01/01/2000' FOR ColumnName

Answer 2

have you tried...

ALTER TABLE dbo.MyTable ALTER COLUMN Created DATETIME NOT NULL DEFAULT whatever;

Answer 3

I think you will need to do this as two 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

ALTER TABLE dbo.MyTable 
ALTER COLUMN Created DATETIME NOT NULL 

Answer 4

You may have to first update all the records that are null to the default value then use the alter table statement.

Update dbo.TableName
Set
Created="01/01/2000"
where Created = NULL