Creating Check Constraint on a Table in SQL Server 2012

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:

CREATE TABLE [dbo].[Employee](
            [EmpID] [int] NULL,
            [EmpName] [varchar](30) NULL,
            [EmpSalary] [int] NULL
            Check([EmpSalary] <=20000)
)

The following is the sample data for the employee Table.

 

 

 

Inserting the value in the table

 

The CHECK constraint specifies empsalary column value greater than and equal to the check constraint (20000) value.

Insert into Employee values('8','Saun', 18000)

Select * from Employee

Output

 

 

 

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

Insert into Employee values('9','Lorren', 21000)

SQL Server returns an error.

 

Output

 

 

Add CHECK Constraint on multiple columns

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

Drop a CHECK Constraint

 

To drop a CHECK constraint, use the following SQL:

ALTER TABLE Employee
Drop  CONSTRAINT [EmpSalary]