Constraints in SQL Server

Constraints are rules and restrictions applied on a column or a table such that unwanted data can't be
inserted into tables. This ensures the accuracy and reliability of the data in the database. We can create
constraints on single or multiple columns of any table. Constraints maintain the data integrity and
accuracy in the table.
SQL Server contains the following 6 types of constraints:
 Not Null Constraint
 Check Constraint
 Default Constraint
 Unique Constraint
 Primary Constraint
 Foreign Constraint

Not Null Constraint:
A Not null constraint restrict 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.
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.
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.
Unique Constraint:
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.
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.
Below is example to create a Primary Key Constraint. Column EmpID of table EmployeeDetails
is specified as Primary Key. Hence EmpID cannot have duplicate and null values.

Create table EmployeeDetails
(
EmpID int PRIMARY Key ,
EmpFirstName varchar(80),
EmpLastName varchar(80),
Department varchar(30),
DepartID int
)

To view the constraints on the table use the below given query, here we have not given the name
to the constraint so a name has been specified by SQL server.
SELECT CONSTRAINT_NAME,
TABLE_SCHEMA ,
TABLE_NAME,
CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='EmployeeDetails'
If we want to provide a user defined name to primarykey, below given query
can be used.
Create table EmployeeDetails
(
EmpID int Constraint empids_PK PRIMARY Key ,
EmpFirstName varchar(80),
EmpLastName varchar(80),
Department varchar(30),
DepartID int
)
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.
Below are two tables EmployeeDetails and EmpSalary. EmpID of EmployeeDetails table's
Primary Key column and EmpID column of EmpSalary table is Foreign Key which references
the EmpID column of EmployeeDetails table.