How to add a default value (constraint) to an existing column in SQL using transact SQL (T-sql).

 

I had an issue recently where rows were not being inserted into one of our SQL tables and I figured out that it was because one of the columns does not allow null values [for good reason]. When originally designed we did not expect the particular field to be submitted without a value, but now we have encountered just such a scenario.  Instead of allowing null values in the column we decided it would be more appropriate to have a default value of "None." 

 

We install everything via scripts so that means for me to roll out the fix to production I had to code the update in transact SQL.  Add to that the fact that I cannot lose any data from the table made the process a little more interesting than I had expected.  I can do this through the GUI in 10 seconds flat or via T-SQL when first creating the table column, but I've never had to do it to an existing table via T-SQL.  Let me tell you...I had a hell of a time trying to figure out the syntax from SQL Books Online.  In fact, I didn't. 

 

My new peer, Shashi, found how buried in a comment to this post dated 11.7.2000.  Go figure. [shrug]

 

Here's the exact syntax:

 

ALTER TABLE TableName_Here WITH NOCHECK ADD CONSTRAINT [ConstraintName] DEFAULT 'DefaultValue_Here' FOR ColumnName_Here 

 

In our environment our installation scripts are run each time we put out a new or updated build which means that we have to make changes without upsetting current data. That also means that we have to check for the existence (or lack) of something before making changes.  So here's the context in which I scripted it:

 

First I check to see if the table already has a default constraint on any of it's columns.  Note that this will return 1 if ANY column has a default value set!  In my case, I know that this is the only column in this table that will have a default value.  If no default constraint is found (returning 0) then I run the ALTER TABLE line, otherwise I don't take any action.

~tod

 

tags: sql transact+sql