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

 

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

 

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



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



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



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



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



-- 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:


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

Readers Feedback:

Did you like this article? If Yes, then please share it: | More Options

Did you like my blog? If Yes, then please help spread the word by sharing this blog with your colleagues, friends, & anyone else in the MSBI Space: Like us on Facebook , Follow us on Twitter .