How to Add a DEFAULT Constraint to an Existing Column in SQL Server

When using SQL Server, sometimes you need to modify an existing table. For the purposes of this article, say you want to add a DEFAULT constraint to an existing column.

To add a DEFAULT constraint to an existing column, use the ALTER TABLE statement and specify the column and the specific constraint that you want to apply.

Example

Here’s an example of adding a DEFAULT constraint to an existing column called TaskDescription:

USE Solutions;
ALTER TABLE Tasks  
    ADD CONSTRAINT taskdesc_default  
    DEFAULT 'TBA' FOR TaskDescription;  
GO

In this case, we changed to the Solutions database first to ensure that we created the constraint against the correct database.

We then went ahead and created a DEFAULT constraint called taskdesc_default for the TaskDescription column, and set the default value to TBA. This means that, whenever a new row is created, if no value has been provided for the TaskDescription column, a default value of TBA will be inserted.

Check the Constraint

You can check that the constraint has been created by running the following statement:

USE Solutions;
SELECT * 
FROM sys.default_constraints;

This lists out all the default constraints for the Solutions database. Again, we changed to the correct database first.

If your database has a lot of constraints, you can always narrow it down to just the constraint you’re interested in:

USE Solutions;
SELECT * 
FROM sys.default_constraints
WHERE name = 'taskdesc_default';