How To Add Default Constraint on SQL Server Table

The DEFAULT constraint is used to set a default value for a column. The default value will be added to all new records, if no other value is specified.

DEFAULT Constraint on CREATE TABLE

Let’s create a table tblEmployee with a column EmpSalary having a default constraint.

CREATE TABLE tblEmployee
(
	EmpID INT PRIMARY KEY NOT NULL,
	EmpFirstName NVARCHAR(20) NOT NULL,
	EmpLastName NVARCHAR(20) NOT NULL,
	EmpAge INT NULL,
	EmpEmail NVARCHAR(50) NULL,
	EmpPhone NVARCHAR(20) NULL,
	EmpSalary INT NULL DEFAULT(50000),
	EmpJoiningDate DATETIME NULL
)
GO

DEFAULT Constraint on ALTER TABLE

ALTER TABLE tblEmployee
ADDCONSTRAINT df_EmpSalary
DEFAULT 50000 FOR EmpSalary

Now let’s insert some values in table tblEmployee with a value for EmpSalary column.

INSERT INTO tblEmployee
    (EmpID,EmpFirstName,EmpLastName,EmpAge,EmpEmail,EmpPhone,EmpSalary,EmpJoiningDate)
VALUES
    (1,'Hana','Moos',35,'hana@gmail.com','0911112545232',65000,'2015-01-01')

By inserting the value for EmpSalary column while this column has a default value of 50000, We can see the result that EmpSalary column has value of 65000 which we write in INSERT INTO statement.

Now, let’s skip the value for EmpSalary column in INSERT INTO statement and see the result.

INSERT INTO tblEmployee
    (EmpID,EmpFirstName,EmpLastName,EmpAge,EmpEmail,EmpPhone,EmpJoiningDate)
VALUES
    (2,'Maria','Anders',35,'maria@gmail.com','0911101545232','2016-01-01')

By skipping the value for EmpSalary column, a default value of 50000 automatically inserted as shown above.

DROP DEFAULT Constraint in SQL Server

To drop a DEFAULT Constraint, we use the following syntax.

ALTER TABLE tblEmployee
DROP df_EmpSalary