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

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

To add CHECK Constraint on multiple columns

ALTER TABLE Employee

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

To drop a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

ALTER TABLE Employee

Drop  CONSTRAINT [EmpSalary]