Creating Check Constraint on a Table in SQL Server 2012

This article will give you an idea of how to use the SQL Server Check Clause and also defines where you can use it.

This article will give you an idea of how to use the Check Clause and also defines where you can use it. SQL Server allows you to define a Check Clause. The Check Clause is used to specify the Check constraints. The Check constraint specifies the conditions for the data inserted into a column of a table in SQL Server. So let's have a look at a practical example of how to use the Check Clause in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio. 
 

CHECK CLAUSE in SQL Server

 
The Check Clause is used to specify the Check constraints. The CHECK constraint is used to limit the value range that can be placed on a column. Or the Check constraint specifies the conditions for the data inserted into a column. Each row inserted into a table or each value updating the value of a column must meet these conditions.
 
Syntax of the Check Clause
 
[constraint column_name]
CHECK  Expression
 
The expression must evaluate to a Boolean value ("true "or "false") and can reference any column in the current table but not another table.
 
Creating a table in SQL Server
 
Now we create a table named employee using:
  1. CREATE TABLE [dbo].[Employee](  
  2.             [EmpID] [intNULL,  
  3.             [EmpName] [varchar](30) NULL,  
  4.             [EmpSalary] [intNULL  
  5.             Check([EmpSalary] <=20000)  
  6. )   
The following is the sample data for the employee Table.
 
Employee-table-in-Sql-Server.jpg 
 
Inserting the value in the table
 
The CHECK constraint specifies empsalary column value greater than and equal to the check constraint (20000) value.
  1. Insert into Employee values('8','Saun', 18000)  
  2.   
  3. Select * from Employee  
Output
 
Check-Constraint-in-SQL-Server.jpg 
 
The table employee contains the column EmpSalary with the corresponding check constraint. After a modification of its existing values or after insertion of a new row it shows the error when a new row value is greater than and equal to the check constraint (20000) value.
 
Inserting the values in the row against the Check Clause
  1. Insert into Employee values('9','Lorren', 21000)  
SQL Server returns an error.
 
Output
 
Check-Constraint-with-insert-in-SQL-Server.jpg
 

Add CHECK Constraint on multiple columns

  1. ALTER TABLE Employee  
  2. ADD CONSTRAINT [EmpSalary] CHECK ([EmpSalary]>0 AND  LEN([EmpName]) < 11)  

Drop a CHECK Constraint

 
To drop a CHECK constraint, use the following SQL:
  1. ALTER TABLE Employee  
  2. Drop  CONSTRAINT [EmpSalary]