SQL-Default Constraints

In this tutorial, you will learn how to use the SQL Server DEFAULT constraint is used to set a default value for a column..

DEFAULT Constraint

When we insert any record in the table, The default value will be added to all new records, if no other value is specified. The DEFAULT is a constraint in SQL which allows users to fill a column with the default or fixed value which you define with default.

The following CREATE TABLE statement defines a DEFAULT constraint on the FirstName column of the Employee table. you can use the CONSTRAINT keyword as follows:

CREATE TABLE Employee(
Eid int NOT NULL,
FirstName varchar(100) DEFAULT 'Rohatash',
LastName varchar(100),
Emailid varchar(100),
Age int,
Salary int,
Departmentid int
);

Output

Now insert data in parent table - Employee. We have not insert any value in FirstName. By defualt name will show.

Insert into Employee(Eid,LastName,Emailid,Age,Salary,Departmentid) 
values(1, 'Kumar', 'Rohatash.mca@gmail.com',101, 6000, 1)

Select * from Employee

Output

DEFAULT Constraint on Multiple Columns

You can define a DEFAULT constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Employee (
Eid int NOT NULL,
FirstName varchar(100) DEFAULT 'Rohatash',
LastName varchar(100),
Emailid varchar(100),
Age int,
Salary int DEFAULT '2000',
Departmentid int
);

Adding DEFAULT Constraint to Existing Columns

To add the DEFAULT constraint to an existing column, The Employee table is already created, use the following SQL Query:

ALTER TABLE Employee
ADD CONSTRAINT df_salary
DEFAULT '200' FOR Salary;

Removing DEFAULT Constraint

To remove the DEFAULT constraint from a column, you can use the ALTER TABLE ALTER COLUMN statement as follows:

ALTER TABLE Employee
DROP CONSTRAINT df_salary;