ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'
Appoint checking the syntax, SQL Server 2005 gives me the following error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'set'.
I was so frustrated and have no idea why would this happened (until now I have completely no idea so please tell me if you know). Turn out I have written the following instead and it's working like a charm!
| ALTER TABLE TableName WITH NOCHECK ADD CONSTRAINT DF_DefaultName DEFAULT 'Default Value' FOR ColumnName |
Yes, it can be frustrating while stuff does not work while you 'd expect it to work. We feel your pain ;-)
ReplyDeleteFor some reason, the syntax of 'column definition' for adding a new column is different than the one for altering a column, and setting a default can not be done by the ALTER COLUMN.
It's unexpected and therefore prone to confusion, but at least it is documented though.
That's the syntax that the SQL Books Online told me, actually, it's a line I copied from Books Online.
ReplyDeleteSo I guess, it should be expected to work, hahaha.
I've just spent the last hour doing exactly what you did originally with the ALTER COLUMN statement, wondering how to get the damn thing to add the default value.
ReplyDeleteThanks for the post.
Thanks for the post. I spent like 30 minutes figuring out what I was doing wrong and start searching articles and found your comments.
ReplyDeleteIt works. Thanks again
I join the club of the others in the comments :-)
ReplyDeleteLots of Thank to You. The command work perfectly.
ReplyDeletethank you
ReplyDeleteThank you Jacqui's, It is really help a lot. you done a great job by helping all of these people
ReplyDeleteI'm joining the same club.. Thnx for the post....
ReplyDeleteFinally - this worked!!! Thanks -
ReplyDeleteThanks it helped me. Pavan.
ReplyDeleteJacqui .. Thanks a lot for this article it helped me a lot.
ReplyDeleteThat got me over one hump.!!!!!!!!!!!!!!!!!!!!!!!
ReplyDeleteI guess what you call the constraint does not matter. Now I am trying to set a description.
I was also 'fooled' by trying to do exactly what the MS website told me. After 10 mins frustration I found this blog which solved it completely. Thanks
ReplyDeleteMe too! Thank you for the post!
ReplyDeleteTHANKS so much for posting this! I was going crazy. :)
ReplyDeleteThanks for the query
ReplyDeleteThanks for the help
ReplyDeleteThanks, I also join the party. After an hour of frustration finally saw your post and it worked.
ReplyDeleteHi,
ReplyDeleteI tried to set the default value for existing column in table but could not set. pls guide me how to set in firebird. Thanks in advance
The Command as,
alter table CUSTOMER
alter column country
set default 'India'
Rahul from india(pune)
ReplyDeleteThanks dear, its worked perfectly
I spent whole night but same problem persisted
now i got relief as my gfrd scolded me as am
experinced programmar till don no how to alter a column
Thank you!
ReplyDeleteI wanted to setup the constraint together with the change NULL -> NOT NULL, it can be done in three step. I document it, allthough it can be trivial for most of the readers, but perhaps useful for someone:
ALTER TABLE TableName WITH NOCHECK
ADD CONSTRAINT DF_DefaultName DEFAULT 'Default Value' FOR ColumnName
update TableName set ColumnName = 'Default Value' where ColumnName is null
ALTER TABLE TAbleName ALTER COLUMN ColumnName ColumnType NOT NULL
I hv spent nearly one hour, and look here n there on all site but finally i got yr post, and yes it worked
ReplyDeletethxx
lol
xx
Thanks!!
ReplyDeleteBecause it would make too much sense given that everything else seems to work via ALTER TABLE xxx ALTER COLUMN xxx. Maybe the powers that be with the SQL Server development team could modify the code in Management Studio so that it elaborates on using ADD CONSTRAINT if trying to add a Default to a column. Hmmm... novel idea or what?
ReplyDeleteThe command work perfectly.
ReplyDeleteThanks for sharing.