SQL Server Constraints with Example

1. Primary key Constraint:

   A Primary key constraint is applied for uniquely identifying rows in a table. It cannot contain Null values and the rest of the 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 an 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 primary key, 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
   )
   
               

   The primary key is called a Composite Primary key when applied to more than one column.

2. Not Null Constraint

   A Not Null constraint ensures that the column in the table cannot have Null values, however, it may have duplicate values. The below given query shows the working of Not Null constraint applied on the DepartID column of the EmployeeDetails table.

   CREATE TABLE EmployeeDetails 
   (
   EmpID INT,
   EmpFirstName VARCHAR(80),
   EmpLastName VARCHAR(80),
   Department VARCHAR(30),
   DepartID INT NOT Null
   )
   
               

   Now while inserting the records into the table when we try to put NULL values into the Not NULL specified column it shows an error.

   INSERT INTO EmployeeDetails
   VALUES
   (1001,'Branda','Paul','IT',null)
               

   

3. Unique Constraint

   Unique Constraint is used to enforce the uniqueness of non Primary key columns. A unique constraint is similar to the Primary key except that it can have null values unless specified not null.

   Below is the example for Unique Constraint applied on the EmpID column of the EmployeeDetails table.

   Create table EmployeeDetails 
   (
       EmpID int unique ,
       EmpFirstName varchar(80),
       EmpLastName varchar(80),
       Department varchar(30),
       DepartID int NOT Null
   )
   
               

   By applying the below query the Constraint type can be identified.

   SELECT CONSTRAINT_NAME,
       TABLE_SCHEMA ,
       TABLE_NAME,
       CONSTRAINT_TYPE
       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   WHERE TABLE_NAME='EmployeeDetails'
   
               

   

4. Check Constraint

   A Check Constraint enforces a restriction on the column for the value to be inserted.

5. Default Constraint

   A Default Constraint is used to assign a constant value to a column, and the user needs not to insert value for such a column.

   Below is create table query in which column TotalMarks is set as Default to 500 and MarksObtained column check the data to be more than 250.

   CREATE TABLE StudentPassMarks 
   (
   StudName VARCHAR(80),
   TotalMarks INT default 500,
   MarksObtained INT CHECK (marksobtained > 250)
   )
   
               

   Hereafter applying the constraints when we try to insert the data, the first row gets inserted because MarksObtained is greater than 250. Whereas the second insert command gets failed as MarksObtained are less than 250. As the total marks column is specified as default we need not put the data while using the insert command.

   INSERT INTO StudentPassMarks (StudName,MarksObtained)
   VALUES
   ('Branda Paul',429)
   INSERT INTO StudentPassMarks (StudName,MarksObtained)
   VALUES
   ('Branda Paul',200)
   
               

   

6. 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 another table is referenced to the Primary Key column. A Foreign Key column can also have reference to the 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.

   CREATE TABLE EmployeeDetails 
   (
       EmpID INT PRIMARY Key ,
       EmpFirstName VARCHAR(80),
       EmpLastName VARCHAR(80),
       Department VARCHAR(30),
       DepartID INT
   )
   
   CREATE TABLE EmpSalary 
   (
       EmpID INT Foreign Key References EmployeeDetails(EmpID) ,
       EmpFullName VARCHAR(80),
       Empsalary INT,
       DepartID INT
   )
   
               

   
   

                   SELECT * FROM EmployeeDetails
               

   

                   Select * from EmpSalary
               

   

   Now if we try to insert another row into EmpSalary table it will raise an error. For more details exception handling see sql exception handling using try catch block.

   INSERT INTO EmpSalary
   VALUES
   (1005,'Petro bond',28000,2)
   
               

   

   Cascading constraints can be used while applying Foreign key Constraint

   ON DELETE [NO ACTION | CASCADE | SET NULL | SET DEFAULT ] ON UPDATE [NO ACTION | CASCADE | SET NULL | SET DEFAULT ]

Syntax for adding a constraint

        Alter table <TableName>
        Add Constraint <ConstraintName> <ConstraintType>
    

Dropping a constraint

        Alter table <TableName>
        Drop constraint <ConstraintName>