Working with Default Constraints in SQL Server

Default Constraints are one of the commonly used constraints in SQL Server. Often people are ambiguous about the usage of Default Constraint in T-SQL. This post explains how a Default Constraint works and the limitations associated with Default Constraints.

— Create a Table
IF OBJECT_ID(‘DefaultConstraintsDemo’) IS NOT NULL
    DROP TABLE DefaultConstraintsDemo
GO

CREATE TABLE DefaultConstraintsDemo(
   ID INT NULL,
    Name VARCHAR(100) NULL CONSTRAINT DF_DefaultConstraintsDemo_Name DEFAULT ‘Unknown’
)
GO

— Insert some data
INSERT INTO DefaultConstraintsDemo(ID, Name)
SELECT 1 AS ID, ‘One’ AS Name UNION ALL
SELECT 2 AS ID, ‘Two’ AS Name UNION ALL
SELECT 3 AS ID, ‘Three’ AS Name UNION ALL
SELECT 4 AS ID, ‘Four’ AS Name

— Check the data in the table
SELECT * FROM DefaultConstraintsDemo

 image

— Insert data with default value
INSERT INTO DefaultConstraintsDemo(ID)
SELECT 5 AS ID UNION ALL
SELECT 6 AS ID

— Check the data in the table
SELECT * FROM DefaultConstraintsDemo

 image

— Override Default Value during Insert
INSERT INTO DefaultConstraintsDemo(ID, Name)
SELECT 7 AS ID, NULL AS Name UNION ALL
SELECT 8 AS ID, NULL AS Name

— Check the data in the table
SELECT * FROM DefaultConstraintsDemo

image

— Set the Name column to Default Value for few existing records
UPDATE DefaultConstraintsDemo
SET Name = DEFAULT
WHERE ID IN (2,3)

image

— Override Default Value during Update
UPDATE DefaultConstraintsDemo
SET Name = NULL
WHERE ID = 4

image

— Alter an existing Default Constraint
ALTER TABLE DefaultConstraintsDemo
DROP CONSTRAINT DF_DefaultConstraintsDemo_Name

ALTER TABLE DefaultConstraintsDemo
ADD CONSTRAINT DF_DefaultConstraintsDemo_Name DEFAULT ‘Not Known’ FOR Name

— Insert data with New Default Value
INSERT INTO DefaultConstraintsDemo(ID)
SELECT 9 AS ID UNION ALL
SELECT 10 AS ID

— Check the data in the table
SELECT * FROM DefaultConstraintsDemo

image

— Set the Name column to New Default Value for few existing records
UPDATE DefaultConstraintsDemo
SET Name = DEFAULT
WHERE ID IN (3,7)

— Check the data in the table
SELECT * FROM DefaultConstraintsDemo

image

— List all the default constraints in a database
SELECT
    OBJECT_NAME(object_id) AS TableName
    , name AS ColumnName
    , ‘Default’ AS ConstraintType
    , OBJECT_NAME(default_object_id) AS ConstraintName
FROM sys.columns
WHERE
    default_object_id <> 0
ORDER BY TableName, ColumnName, ConstraintName

If you run the above query in AdventureWorks database, then the results will be as follows:
image

— List all the default constraints in a table
SELECT
    OBJECT_NAME(object_id) AS TableName
    , name AS ColumnName
    , ‘Default’ AS ConstraintType
    , OBJECT_NAME(default_object_id) AS ConstraintName
FROM sys.columns
WHERE
    default_object_id <> 0
    AND OBJECT_NAME(object_id) = ‘DefaultConstraintsDemo’
ORDER BY TableName, ColumnName, ConstraintName

— Clean up the Demo Table
DROP TABLE DefaultConstraintsDemo
GO

Here are a few things to note about the Default Constraints:
  • Default Constraints cannot be disabled.
  • Default Constraints cannot be altered. Alternatively, you can drop and re-create the Default Constraints as demonstrated above.
Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on December 30, 2010, in Code Snippets, SQL Constraints, SQL Server, T-SQL, T-SQL Snippets. Bookmark the permalink. Leave a comment.

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s