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
- Advanced SQL Queries For Practice With Solution
- SQL Queries For Practice With Solution
- SQL Interview Questions and Answers
- STORED PROCEDURE in SQL Server
- How To Join Tables Data in SQL Server
- How to use Transaction in SQL Stored Procedure
- Difference Between IN and NOT IN Operators in SQL
- How To Modify Date in SQL Using DATEADD
- How To Get Year From Date in SQL Server
- How To Get Month From Date in SQL Server
- How To Get Day From Date in SQL Server
- How To Use ROW_NUMBER Function in SQL
- Date and Time Functions in SQL Server
- How To Find Nth Highest Salary in SQL Server
- How to Backup Table Using SELECT INTO Statement
- How To Use HAVING Clause in SQL Server
- Aggregate Functions in SQL Server
- How To Group Data Using Group By in SQL Server
- How To Truncate Table in SQL Server
- How To Delete Data From Table in SQL Server
- How To Update Table Data in SQL Server
- How To Sort Data Using Order By Clause in SQL
- How To Select Distinct Records in SQL Server
- How to Filter Data From Table in SQL Server
- Round Off Values Using Ceiling and Floor in SQL
- How To Find Square Root Of A Number in SQL Server
- How To Select Data From Table in SQL Server
- How To Insert Data in SQL Server Table
- How To Add NOT NULL Constraint in SQL Server
- How To Add Check Constraint on SQL Server Table
- How To Add Default Constraint on SQL Server Table
- Unique Key Constraint in SQL Server
- How to add Foreign Key Constraint in SQL Server
- How To Add Identity To SQL Server Table Column
- How to add Primary Key Constraint in SQL Server
- How To Create Alter and Drop Table in SQL Server
- How To Create Alter and Drop Database in SQL