Constraints in sql server

In simple words, Constraints define rules those we implement to avoid insertion of any data that does not follow the rule. By using the Constraint, we can validate the data and if complies the rule, It will be inserted in table otherwise rolled back. Constraints are used to maintain Data Integrity. They make sure the data entered follow the rules we have defined.

There are different types of Constraints available in SQL Server.

Ø  Not Null Constraint :- Ensures that a column cannot have a NULL value

Ø  Check Constraint :- Ensures that all values in a column satisfies a specific condition

Ø  Default Constraint :- Sets a default value for a column when no value is specified

Ø  Unique Constraints :- Ensures that all values in a column are different

Ø  Primary Key Constraint: - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

Ø  Foreign Key Constraint:- Uniquely identifies a row/record in another table

Not null Constraint

A Not null constraint restricts the insertion of null values into a column. If we are using a Not Null Constraint for a column then we cannot ignore the value of this column during an insert of data into the table, however it may have duplicate values. 

See the example

Here I am creating a table with not null constraint.

create table Emp_Test_Constraint ( EmpID INT, FName VARCHAR(50), LName VARCHAR(50), Dept VARCHAR(30), DeptID INT NOT Null )

Table created successfully. Now I am inserting a record with DeptID as null and see the result.

INSERT INTO Emp_Test_Constraint (EmpID,FName,LName,Dept,DeptID) VALUES (1,'BageshKumar','Singh','IT',null)

When we inserting records in the table we will get below error

Check Constraint

A Check constraint checks for a specific condition before inserting data into a table. If the data passes all the Check constraints then the data will be inserted into the table otherwise the data for insertion will be discarded. The CHECK constraint ensures that all values in a column satisfies certain conditions. For example we want to that ID will be positive value.

Before setting check constraint on the table

As we know ID will not in negative value.

Now I am going to add check constraint on the EmpID column so it will avoid to inserting any negative value for the EmpID column.

create table Emp_Test_Constraint ( EmpID INT Constraint Check_EmpID Check(EmpID>0), FName VARCHAR(50), LName VARCHAR(50), Dept VARCHAR(30), DeptID INT NOT Null )

Table creates successfully.

Now I am inserting a record which empID is -1 and see the result.

INSERT INTO Emp_Test_Constraint (EmpID,FName,LName,Dept,DeptID) VALUES (-1,'BageshKumar','Singh','IT',1)

See the output

 Default Constraint

Specifies a default value for when a value is not specified for this column. If in an insertion query any value is not specified for this column then the default value will be inserted into the column. In this given example I am setting by default value of department as ‘IT’. Let’s see the example.

create table Emp_Test_Constraint ( EmpID INT Constraint Check_EmpID Check(EmpID>0), FName VARCHAR(50), LName VARCHAR(50), Dept VARCHAR(30) default 'IT', DeptID INT NOT Null )

Table is created. Now I am inserting record in the table and I am not supplying value of Dept. let’s see the result.

INSERT INTO Emp_Test_Constraint (EmpID,FName,LName,DeptID) VALUES (1,'BageshKumar','Singh',1)

Record inserted successfully. Now see the value in table.

See by default value of Dept is IT.

Unique Constraints

It ensures that each row for a column must have a unique value. It is like a Primary key but it can accept only one null value. In a table one or more column can contain a Unique Constraint.

create table Emp_Test_Constraint ( EmpID INT unique , FName VARCHAR(50), LName VARCHAR(50), Dept VARCHAR(30) default 'IT', DeptID INT NOT Null )

Now I am inserting some records

INSERT INTO Emp_Test_Constraint (EmpID,FName,LName,Dept,DeptID) VALUES (1,'BageshKumar','Singh','HR',1) INSERT INTO Emp_Test_Constraint (EmpID,FName,LName,Dept,DeptID) VALUES (NULL,'BageshKumar','Singh','HR',1)

See the value.

Now when we are inserting another record with Null or EmpId 1 it will not insert and throw unique key constraint error.

Primary Key Constraint

A Primary key constraint is applied for uniquely identifying rows in a table. It cannot contain Null values and rest of table data should be unique. While creating a table if we do not specify a name to the constraint, sql server automatically assigns a name to the constraint. We can have only one primary key on a table.

create table Emp_Test_Constraint ( EmpID INT primary key , FName VARCHAR(50), LName VARCHAR(50), Dept VARCHAR(30) default 'IT', DeptID INT NOT Null )

Now I am inserting a record in table.

 

Now I am inserting same record second time.

Read difference between Primary keyand unique key constraint

 http://bageshkumarbagi-msbi.blogspot.in/2017/05/difference-between-primary-key-and.html

Foreign Key Constraint

A Foreign Key Constraint is used to establish a relationship between two tables where one column is a Primary Key of the table and the other column from other table is referenced to the Primary Key column. A Foreign Key column can also have reference to Unique Key column of another table.

create table Emp_Test_Constraint ( EmpID INT primary key , FName VARCHAR(50), LName VARCHAR(50), DeptID INT Foreign Key References DEPT(DeptID) ) CREATE TABLE DEPT     (         DeptID INT primary key,         Dept VARCHAR(30)     )

Inserting record in DEPT table.

INSERT INTO DEPT values(1,'IT') INSERT INTO DEPT values(2,'HR')

Now I am inserting some record in Emp_Test_Constraint

INSERT INTO Emp_Test_Constraint (EmpID,FName,LName,DeptID) VALUES (1,'BageshKumar','Singh',1)

This record inserted successfully.

Now I am inserting other record and using DeptID as 5 which is not in DEPT table. It will throw an error.