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';