Post
Ask Question

Understanding SQL Server Constraints

Constraints enforce a set of rules in the database and maintain the integrity of the data. Constraints can be created on the table or column. Constraints can be created either while table creation or with alter table statement. Constraints created on column validate the data before adding to a table. If the values satisfy the condition of a constraint then only it allows inserting/updating the data otherwise aborts the operation.
 
There are 6 main constraints available in SQL Server. We will learn about these one-by-one.
  1. Unique key constraint
  2. Primary key constraint
  3. Foreign key constraint
  4. Not Null constraint
  5. Check constraint
  6. Default constraint

Unique Key

 
Unique key constraints can be created on the column and they allow only unique values to be inserted in the column. Unique key constraints do not allow you to insert duplicate values in the column or set of columns; it may allow a null value though, in a column. 
 
Unique key constraint can be set with the Create Table statement,
 
Syntax
  1. CREATE TABLE Employee (  
  2.    Id INT,  
  3.    Name VARCHAR (100),  
  4.    MobileNumber VARCHAR(20),  
  5.    PANNumber VARCHAR(10) NOT NULL UNIQUE,  
  6.    Salary INT  
  7. );  
Or unique key constraint can be set with the Alter Table statement also.
 
Syntax
  1. ALTER TABLE Employee ADD CONSTRAINT Unq_EmpPAN UNIQUE (PANNumber);  

Primary Key

 
Primary key allows only unique values inserted in the column and column cannot have null. Primary key constraints do not allow you to insert duplicate values in the column or set of columns, and it also does not allow any null value. The difference between Unique key constraint and Primary key constraint is unique key allows a null value to be inserted and the primary key constraint does not allow any null value. A primary key can also make as a composite key.
 
A primary key constraint can be set with Create Table statement.
 
Syntax
  1. CREATE TABLE Employee (  
  2.    Id INT PRIMARY KEY,  
  3.    Name VARCHAR (100),  
  4.    MobileNumber VARCHAR(20),  
  5.    PANNumber VARCHAR(10),  
  6.    Salary INT  
  7. );  
Composite primary key syntax -
  1. CREATE TABLE Employee (  
  2.    Id INT NOT NULL,  
  3.    Name VARCHAR (100),  
  4.    MobileNumber VARCHAR(20),  
  5.    PANNumber NOT NULL VARCHAR (10),  
  6.    Salary INT,  
  7.    CONSTRAINT pk_Emp_comp_Id_PAN (Id, PANNumber)  
  8. );  
Primary key constraint can be set with Alter Table statement too.
 
Syntax
  1. ALTER TABLE Employee ADD PRIMARY KEY (Id);   

Foreign Key

 
Foreign key is used to make a relationship between two tables and enforce referential integrity. A foreign key is used to join two tables and generally, the primary key of a table becomes foreign key to another table and tables are joined by these key columns. Foreign key also maintains the referential integrity.  Referential Integrity ensures that the value should be available first in the primary key table and then only it can be added to the foreign key table and the data should be removed first from the foreign key table and them from the primary key table.
 
Foreign key constraint can be set with the Create Table statement.
 
Syntax
  1. CREATE TABLE EmployeeAddress (  
  2.    AddId INT PRIMARY KEY,  
  3.    Address VARCHAR (200),  
  4.    EmpId INT FOREIGN KEY REFERENCES Employee (Id)  
  5. );  
And, with the Alter Table statement also.
 
Syntax
  1. ALTER TABLE EmployeeAddress ADD CONSTRAINT FK_EmpAddr FOREIGN KEY (EmpId) REFERENCES Employee (Id);  

Not-Null

 
A not-null constraint can be applied on column level and it ensures that the column cannot have a null value. Not null constraints ensure that any operation insert/update must supply a value to the column.
 
A not-null constraint can be set with the Create Table statement.
 
Syntax
  1. CREATE TABLE EmployeeAddress (  
  2.    AddId INT PRIMARY KEY,  
  3.    Address VARCHAR (200) NOT NULL,  
  4.    EmpId INT NOT NULL FOREIGN KEY REFERENCES Employee (Id)  
  5. );  
A Not-null constraint can be set with the Alter Table statement too.
 
Syntax
  1. ALTER TABLE EmployeeAddress ALTER COLUMN Address VARCHAR (200) NOT NULL;  

Check

 
Check constraints enforce that the value for the column satisfies some condition. If the value does not meet the condition, the check constraints will abort the operation. The Check constraint can be applied on the column level and it ensures that the value which is being inserted meets the condition defined in the constraints. For example, the Check constraint can be set on the Employee age column to check that the age should not be below than 18. Also, the check can set on the Salary column so that the salary value cannot be zero.
 
Check constraint can be set with the Create Table statement.
 
Syntax
  1. CREATE TABLE Employee (  
  2.    Id INT,  
  3.    Name VARCHAR (100),  
  4.    MobileNumber VARCHAR (20),  
  5.    PANNumber VARCHAR (10),  
  6.    Age INT CHECK (Age > >=18),  
  7.    Salary INT   
  8. );  
Check constraint can also be set with the Alter Table statement.
 
Syntax
  1. ALTER TABLE Employee ADD CHECK (Salary > 0);  

Default

 
Default constraint sets the default value for the column if no value is provided.
 
Default constraint can be set with the Create Table statement.
 
Syntax
  1. CREATE TABLE Employee (  
  2.    Id INT,  
  3.    Name VARCHAR (100),  
  4.    MobileNumber VARCHAR (20),  
  5.    PANNumber VARCHAR (10),  
  6.    JoiningDate DateTime Default GETDATE ()  
  7. );  
Default constraint can be set with the Alter Table statement also.
 
Syntax
  1. ALTER TABLE Employee ADD CONSTRAINT Emp_DefaultJoining DEFAULT (GETDATE ()) FOR JoiningDate;   


Similar Articles