ADD DEFAULT TO EXISTING COLUMN

Question

Post reply

ADD DEFAULT TO EXISTING COLUMN

MostInterestingMan

SSCertifiable

Points: 6291
More actions
July 26, 2010 at 1:50 pm

#225391

Hi,
I have an existing column defined as NVARCHAR(10) NULL with no CONSTRAINT to which I would like to add a DEFAULT CONSTRAINT. However the syntax below from BOL generates the following error:
BOL syntax:
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'
Error Msg:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SET'.
Can anyone explain why the above ALTER STATEMENT errors out?
Thanks,
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQL Marathoner SSC-Addicted Points: 417 More actions · Answer 1

Try "Add Contraint" instead of "alter column":

ALTER TABLE MyTable ADD CONSTRAINT default_name DEFAULT 'my default value' FOR MyColumn;

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 2

I believe the actual format is something like this:

ALTER TABLE Table1

ADD CONSTRAINT Cont_XYZ DEFAULT('hello') for Column1

Fraggle

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 3

You are right, Fraggle. The syntax ALTER TABLE table_name ADD CONSTRAINT DF_constraint_name DEFAULT('hello') FOR column_name works perfectly. I was just wondering why the BOL syntax was giving that error.

Thanks

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

jvanderberg SSCertifiable Points: 6762 More actions · Answer 4

I suspect you're looking at the BOL for the wrong version of SQL Server. I found the BOL you are referencing, but it is for SQL Server Compact: http://msdn.microsoft.com/en-us/library/ms174123(SQL.100).aspx. The BOL for SQL Server 2008 looks quite a bit different: http://msdn.microsoft.com/en-us/library/ms190273.aspx

--J

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 5

You are right jvanderberg, it is the syntax for SQL Server Compact.

Thanks for taking a look.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

jvanderberg SSCertifiable Points: 6762 More actions · Answer 6

No problem. I did the same thing, and thought the BOL was crazy. I wanted to know what was up too, so I did a little digging and realized I was looking at the wrong documenation.

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 7

Wow, who would have thought that the Compact Ed would use different sytax for this. Crazy.

Fraggle.

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 8

I know.

I have not used the Compact edition much but never knew there were differences in syntax...very interesting.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends