SQL DEFAULT constraint is used to provide a default value for a columns, when the insert into statement does not provide a specific value to columns.
In case when value does not provide to column, SQL Server assign a NULL value to the column. So for such case, you can use the SQL Server Default constraint to assign a default values to column.
Lets look at an example using SQL Default Constraint.
Creating a Default Constraint while creating a new table
Lets create a table named as EmployeeInfo and add a Default constraint on column Department, a default value ‘Not Available’ will be added to new records if no other value is specified for department.
CREATE TABLE dbo.EmployeeInfo(
EmpId INT NOT NULL PRIMARY KEY,
EmpName VARCHAR (50) NOT NULL,
EmpCode VARCHAR(10) NOT NULL,
Department VARCHAR (50) NOT NULL DEFAULT('Not Available'),
)
OR
--If you want to provide a name for your default constraint
--then you can give name, here name given to default constraint
--is DF_EmpInfo_Dpt
CREATE TABLE dbo.EmployeeInfo(
EmpId INT NOT NULL PRIMARY KEY,
EmpName VARCHAR (50) NOT NULL,
EmpCode VARCHAR(10) NOT NULL,
Department VARCHAR(50) NOT NULL
CONSTRAINT DF_EmpInfo_Dpt DEFAULT('Not Available')
)
You can also verify whether a default constraint is created or not on table, using following statement.
SELECT t.name as TableName, c.Name as ConstraintName, c.Type_Desc, c.Definition FROM sys.default_constraints c INNER JOIN sys.tables t ON t.object_Id =c.Parent_object_Id WHERE OBJECT_NAME(c.parent_object_id) = N'EmployeeInfo';
You can see, it returns table name and constraint created on table. There you can see a default constraint named DF_EmpInfo_Dpt that you just created on table.
Lets insert a records in table, here we are not inserting a values for Department column purpously as we want default constraint to assign default values for Department automatically.
INSERT INTO dbo.EmployeeInfo (EmpId, EmpName, EmpCode) VALUES (1, 'Jack Mark', 'XC7843'), (2, 'Rozer Larrey', 'EK7823'), (3, 'Mahesh Ram', 'FV7921')
You can verify records in table whether the default value for Department is assigned or not.
Lets select records from table EmployeeInfo .
You can see, defaults value ‘Not Available’ are provided to Department Column.
SELECT * FROM EmployeeInfo
Creating a Default Constraint on existing table
Now we will create a Default constraint on Existing table, suppose you have a table EmployeeData in database.
CREATE TABLE dbo.EmployeeData( EmpId INT NOT NULL PRIMARY KEY, EmpName VARCHAR (50) NOT NULL, EmpCode VARCHAR(10) NOT NULL, Department VARCHAR (50) NULL )
Now we will create a Default constraint on column Department. The default value is given as ‘Finance’ , If no values is given to column.
Follwing statement creates a Default Constraint using Alter table Statement.
ALTER TABLE dbo.EmployeeData
ADD CONSTRAINT DF_EmployeeData_Department DEFAULT('Finance') FOR Department;
Lets insert records in table EmployeeData, Again we will not provide value for Department as we want to see whether the default value is provided by default constraint or not.
INSERT INTO dbo.EmployeeData(EmpId, EmpName, EmpCode) VALUES (1, 'Manoj Rawat', 'YC8i43'), (2, 'Sanay Hanry', 'QW7G23'), (3, 'Lokesh Verma', 'GH79R1')
Lets select records from table, You can see default value ‘Finance’ is provided to Department column.
SELECT * FROM dbo.EmployeeData
So far we have seen, if value is not provided for Department column then Default Constraint provides a default value to column, and if value for department column is provided by user then Default constraint will provide any value.
Lets insert a value for Department in table.
INSERT INTO dbo.EmployeeData(EmpId, EmpName, EmpCode, Department) VALUES (4, 'Milian Kumar', 'ZC9JJ45', 'Sales')
Now we will select a records from table to verify whether the Default value ‘Finance’ is given to Department column or a value ‘Sales’ that is provided by user.
SELECT * FROM dbo.EmployeeData
In above result set you can see, if value for Department column is provided then Default constraint does not assign any value, means it assign value only if no value is given to column.
Deleting the Default constraint
If you wish to delete Default Constraint then you can also delete Default Constraint from table column using drop constraint statement as given below.
Lets Delete a Default Constraint from table EmployeeData that you just created above. But before Deleting a Constraint from table you must know the constraint name that you are going to delete.
If you do not know constraint name then you can use the same query that we have used above to see all the constraints created on table, as we want to delete default constraint so also check type of constraint it should be default.
Following statement, returns all the constraint created on table, and there you can see the default constraint that is created on table and default value for constraint is ‘Finance’.
SELECT t.name as TableName, c.Name as ConstraintName, c.Type_Desc, c.Definition FROM sys.default_constraints c INNER JOIN sys.tables t ON t.object_Id =c.Parent_object_Id WHERE OBJECT_NAME(c.parent_object_id) = N'EmployeeData';
You can see above statement result, Default constraint name is DF_EmployeeData_Department.
Lets Delete the Constraint, using following Statement.
ALTER TABLE dbo.EmployeeData DROP CONSTRAINT DF_EmployeeData_Department;
Lets insert record into table EmployeeData, to see if still default value is given by Default constraint for Department column or not.
INSERT INTO dbo.EmployeeData(EmpId, EmpName, EmpCode) VALUES (5, 'Rohit Aggarwal', 'VB9JJE5')
Lets select the records from table, and this time default value to department column should not be given as Default Constraint is deleted from table column.
SELECT * FROM dbo.EmployeeData
As you can see, this time value for Department column is null, means default constraint is deleted from department column.
Also Read..
Cascading referential integrity in Foreign Key
