Default Constraint In SQL Server

Why use Default Constraint?

We have already employee1 table and if you check the Dept column it is nullable.

When we try to insert a record in the table without providing a Dept column value, the Null value is assigned to the Dept column. Let's check with an example.

insert into employee1(EID,FirstName,LastName,Loc,Salary) values(33,'Chaitanya','Patil','Warje',90000)

select * from employee1

If we check with the Dept column value, We found null value.

But If the department column is not nullable the above statement throws an error.

alter table employee1 alter column Dept varchar(20)  null

insert into employee1(EID,FirstName,LastName,Loc,Salary) values(33,'Chaitanya','Patil','Warje',90000)//Cannot insert the value NULL into column 'Dept', table 'pratikshadb.dbo.employee1'; column does not allow nulls. INSERT fails.

Default Constraint

• Used to specify the default values to the column.

• This specified default value is used by SQL server when the value is absent in the insert query for that column.

• To provide a default value to column for string we need to provide a single quote and for numbers we don’t require.

Add Default constraint using create Statement

create table students(studID Int Primary Key,studName varchar(50),email varchar(50) not null default '')

If we try to insert a null value we will get an error.

insert into students values(1,'cd',null)

insert into students (studID,studName) values(1,'CD')

Here We are not going to mention email details as the column is not nullable then also it is allowed to insert records in the table. This is happening because of Default constraint.

SELECT * from students

Add Default Constraint using alter command

Syntax:

ALTER TABLE <tableName>

ADD CONSTRAINT <constraintName> DEFAULT <default_value> FOR <columnName>;

Example:

alter table employee1 add constraint DF_employee1_Dept default '' for Dept

insert into employee1(EID,FirstName,LastName,Loc,salary) values(100.500,'Rohan','Mane','Sangali',15000)

select * from employee1

Drop/Delete Default Constraint:

Syntax:
Alter table <tableName> DROP Constraint <constrain-name>

Example:

Alter table employee1 DROP Constraint DF_employee1_Dept

insert into employee1(EID,FirstName,LastName,Loc,salary) values(100.500,'Rohan','Mane','Sangali',15000)  //It is inserting null for Dept column