How to write sql to set alter a column's default value in sql server 2005?

Question

I have a table [Product] with a column [CreateTime] datetime null, and is has some data already.

How can I set the column [CreateTime] 's default value to getdate(), and make the new added data to have a default value getdate() for column [CreateTime].

Answer 1

You cannot change a default - you will need to first drop it, and then recreate it.

In order to drop it, you need to know its name, and then use

ALTER TABLE dbo.Product
  DROP CONSTRAINT yourOldDefaultConstraint

Once you've done that, you can add a new default constraint, and in order to apply it to existing rows, use the "WITH VALUES" part:

ALTER TABLE dbo.Product
   ADD CONSTRAINT NewDefaultConstraintName
   DEFAULT GetDate() FOR CreateTime WITH VALUES

Oops - sorry, the "WITH VALUES" only seems to work if you create a DEFAULT constraint at the time you create the table, or if you add the column - it doesn't seem to get applied to an existing column.

In this case you would just have to follow your ALTER TABLE statement with something like this:

UPDATE dbo.T_Product
SET CreateTime = GETDATE()
WHERE CreateTime IS NULL

That should do the trick, too!

Marc