How To Use Default Constraint In SQL Server

Constraints in SQL database maintains data integrity and accuracy. SQL Default Constraint is used to assign a default value to a column when no value is specified. Using Default constraint we can have a meaningful value instead of storing NULL or Empty value. It reduces the presence of NULLs when applicable by improving data consistency.

A column having some meaningful data using default constraint is greatly beneficial rather than having no data. For an example, when an employee joins a new organization – his status, by default set to Active. Thus each time, instead of supplying Active status value while inserting new employee record. We can use Default constraint on a Status column with the default value set to Active.

Default constraint can be added while creating a new table or we may add to a column in an existing table using ALTER TABLE.

SQL Default Constraint – Points To Ponder

1.Used to specify a default value instead of NULL or unsuitable value using default constraint.

2.keyword default is used in SQL to specify a default value when no value is specified.

3.Use ALTER TABLE to add a default constraint to the existing table.

4.Cannot be added on TimeStamp and IDENTITY typed columns.

5.Can’t modify existing default constraint, we must drop and re-create default constraint every time if a requirement has changed.

6.Can specify string, number, date, or decimal value in default constraint to work with most SQL data types.

Creating Default Constraint with New Table

Generally, we can define, Default constraint while creating a new table. In addition to this, a good practice to always specify constraint name – otherwise SQL Server will assign a default name, which is considerably tough to read.

In this following example, we are creating an Employee table with added default constraint on Status and CreatedDate column. In default constraint definition you can see the default value Active for Status column and dynamic default value using GETDATE function for CreatedDate column.

Using the default keyword within an INSERT statement to use DEFAULT constraint values.

USE tempdb;
GO

IF OBJECT_ID('Employee','U') IS NOT NULL
DROP TABLE Employee;

CREATE TABLE Employee
(
  Id	       INT	IDENTITY (1,1) 
 ,Name	       VARCHAR(50)    NOT NULL
 ,City	       VARCHAR(50)    NOT NULL
 ,Status       VARCHAR(10)    NOT NULL CONSTRAINT DF_Emp_Status	     DEFAULT 'Active'
 ,CreatedDate  DATETIME2      NOT NULL CONSTRAINT DF_Emp_CreatedDate DEFAULT GETDATE()
);
GO

INSERT INTO Employee (Name, City, Status, CreatedDate) VALUES
 ('Vishal', 'Mumbai', default, default) -- use default keyword to supply default constraint values
,('Sangram', 'Pune',  default, default)
,('Atul', 'Mumbai', 'InActive', GETDATE()); --Over riding default constraint values

SELECT *
FROM   Employee;

Creating Default Constraint On Existing Column

Likewise, we could have a requirement to add Default constraint on an existing column. In this example, the Employee table is already created. Supposedly, we need to add default constraint on City column. Considering most of our employees are from Mumbai city.

In the following query using ALTER TABLE and adding Default constraint.

Use tempdb;
GO

ALTER TABLE Employee
ADD CONSTRAINT DF_Emp_City
DEFAULT 'Mumbai' FOR City;
GO

INSERT INTO Employee (Name, City, Status, CreatedDate) VALUES
 ('Shailesh', default, default, default) -- use default keyword to supply default constraint values

 SELECT *
 FROM   Employee;

Overriding Default Constraint Values

In case you have relevant values for all the fields in a table. Then you can specify them within INSERT statement to override default constraint. In this example, we have specified values for all the columns having Default constraint. Therefore, default values will be overwritten.

INSERT INTO Employee (Name, City, Status, CreatedDate) VALUES
 ('Chetan', 'Pune', 'Active', GETDATE()) -- use default keyword to supply default constraint values

 SELECT *
 FROM   Employee;

View All Default Constraints On a Table

Using following query, we can fetch the list of default constraints created on a table.

--List all Default Constraint 

SELECT name AS DefaultConstraintName,
       OBJECT_NAME(parent_object_id) AS TableName,
       COL_NAME(parent_object_id, parent_column_id) AS ColumnName,
       type_desc   AS Type,
       create_date AS CreatedDate,
       definition  AS DefaultValue
FROM   sys.default_constraints --system view storing all default constraint info
WHERE  OBJECT_NAME(parent_object_id) = 'Employee';

Especially if you want to see all the constraints created on a table using a system created stored procedure then we can use sp_helpconstraint by passing the table name as input to fetch the list of constraints available.

--View All Constraint 

EXEC sp_helpconstraint 'Employee';

Drop Default Constraint

For instance, we have decided to not make Mumbai as default city value. As a result, we can drop default constraint defined on City column.

IF OBJECT_ID('DF_Emp_City','D') IS NOT NULL
    ALTER TABLE Employee DROP CONSTRAINT DF_Emp_City, COLUMN City
GO 

References

Beginners Guide to CHECK Constraints in SQL Server
How To Use Unique Constraint In SQL Server

Summary

Firstly, we have learn about uses of Default constraint in SQL Server. Addition to this, we have seen examples using Default constraint on columns to insert default value using keyword default instead of specifying any value. If you like this post you can visit Tech-Recipes Database archives.