Issue with setting default value for a column (sql server 2005)

Question

The following syntax won't work:

ALTER TABLE MyCustomers 
ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'

Error message:

Incorrect syntax near the keyword 'set'.

What gives?

Thanks.

You need to check the correct documentation: msdn.microsoft.com/en-us/library/ms190273(v=sql.105).aspx. You linked to the Compact edition of SQL Server, but you're probably using the normal one... — MicSim
– MicSim, Commented May 16, 2013 at 14:08

Community · Accepted Answer · 2017-05-23 12:11:17Z

1

The following will work on SQL Server, and has the added benefit of naming the constraint:

ALTER TABLE MyCustomers 
ADD CONSTRAINT DF_SomeName DEFAULT 'A. Datum Corporation' FOR CompanyName;

CommunityBot

11 silver badge

answered May 16, 2013 at 14:02

Duffmaster33

1,1889 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sorry - but the second statement is not valid T-SQL code - you cannot define a default constraint using a SET DEFAULT clause....

You are correct, I was looking at the document given in the question, which seems to be incorrect. I fixed my answer. Thank you.