Wednesday, 15 August 2007

Add default value to existing column

I have spent quite a bit of time trying to figure out how to add a default constraint to an existing column in SQL Server (in T-SQL). I found the following from the SQL Server 2005 Books Online:

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

26 comments:

  1. Yes, it can be frustrating while stuff does not work while you 'd expect it to work. We feel your pain ;-)

    For 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.

    ReplyDelete
  2. That's the syntax that the SQL Books Online told me, actually, it's a line I copied from Books Online.

    So I guess, it should be expected to work, hahaha.

    ReplyDelete
  3. 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.

    Thanks for the post.

    ReplyDelete
  4. Thanks for the post. I spent like 30 minutes figuring out what I was doing wrong and start searching articles and found your comments.

    It works. Thanks again

    ReplyDelete
  5. I join the club of the others in the comments :-)

    ReplyDelete
  6. Lots of Thank to You. The command work perfectly.

    ReplyDelete
  7. Thank you Jacqui's, It is really help a lot. you done a great job by helping all of these people

    ReplyDelete
  8. I'm joining the same club.. Thnx for the post....

    ReplyDelete
  9. Finally - this worked!!! Thanks -

    ReplyDelete
  10. Thanks it helped me. Pavan.

    ReplyDelete
  11. Jacqui .. Thanks a lot for this article it helped me a lot.

    ReplyDelete
  12. That got me over one hump.!!!!!!!!!!!!!!!!!!!!!!!

    I guess what you call the constraint does not matter. Now I am trying to set a description.

    ReplyDelete
  13. 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

    ReplyDelete
  14. Me too! Thank you for the post!

    ReplyDelete
  15. THANKS so much for posting this! I was going crazy. :)

    ReplyDelete
  16. Thanks for the help

    ReplyDelete
  17. Thanks, I also join the party. After an hour of frustration finally saw your post and it worked.

    ReplyDelete
  18. Hi,
    I 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'

    ReplyDelete
  19. Rahul from india(pune)
    Thanks 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

    ReplyDelete
  20. Thank you!

    I 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

    ReplyDelete
  21. I hv spent nearly one hour, and look here n there on all site but finally i got yr post, and yes it worked
    thxx

    lol

    xx

    ReplyDelete
  22. Because 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?

    ReplyDelete
  23. The command work perfectly.
    Thanks for sharing.

    ReplyDelete