Change a Nullable column to NOT NULL with Default Value

sql sql-server alter-table alter-column
Jim B picture Jim B · Jul 6, 2010 · Viewed 166.9k times · Source

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

Abe Miessler picture Abe Miessler · Jul 6, 2010

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

Related questions

Rename column SQL Server 2008

I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL. ALTER TABLE table_name RENAME COLUMN old_name to new_name; This statement doesn't work.

Read More
Adding an identity to an existing column

I need to change the primary key of a table to an identity column, and there's already a number of rows in table. I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine …

Read More
Altering column size in SQL Server

How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5)

Read More