You are currently viewing How To Add Default Constraint on SQL Server Table

How To Add Default Constraint on SQL Server Table

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
    

Recommended Readings

Buy Best Stationery Products in Pakistan

Piano Ballpoint Pen

Piano Ballpoint Pen

ORO Trimetal Pencil

ORO Trimetal

Dollar Clipper Pen

UHU 10×7 ml Pack of 10 PCs

UHU 10×7 ml Pack of 10 PCs

Glue Stick 8g Pack Of 12 PCs

Glue Stick 8g

Color Pencils Box of 12 PCs

Deer Multi Color Pencils