Add a default constraint to an existing field with values

Question

I'm trying to add a default constraint to an existing field in SQL Server using the following T-SQL:

alter table Extra add constraint DF_Extra_ExternalRef DEFAULT ('*') for ExternalRef with values

This adds the default constraint but fails to update existing records with null values.

I'm using SQL Server 2005.

Answer 1

I've only ever seen WITH VALUES used this way when adding a new column (and this is all that is documented). If you're adding a constraint to an existing column, I think WITH VALUES is a no-op. Therefore:

ALTER TABLE dbo.Extra ADD CONSTRAINT DF_Extra_ExternalRef 
 DEFAULT ('*') FOR ExternalRef;
UPDATE dbo.Extra SET ExternalRef = '*' WHERE ExternalRef IS NULL;