Check Constraint:

Check constraint is used to limit the range of the values that can be entered for the column.

So in order to implement the same let’s take an example of the below mentioned table:

We have a table called Customer which contains Customer details and SIM Count which denotes the number of SIM user has subscribed. We want to restrict the user to only buy or subscribe maximum 3 SIM cards. Here as per current records we have SIM Count as Null which is making or database inconsistent because if user has not subscribed to the SIM it’s should be zero not null. Our table column SIM Count is an int data type and it allows NULL. So that is why it allows null and other numbers to be inserted. But we want to restrict this wrong entry and only 1 to 3 SIM Count can be inserted into the SIM Count column.

Applying Check constraint using Designer in SQL server

1. Go to table structure in SQL server as shown below:
   

1. Right click on Constraint folder and click on Add Constraint below window will open.
   

Constraint is basically are Boolean expressions which result true or false.

1. Add expression to the Expression window.
   

   
   

   
   

   Click on close button.
   

2. Once we have successfully created our check constraint it’s now time to test the same.
   

If I try to execute the below query with above mentioned check constraint enabled it will fail:

insert
into
Customer
VALUES(‘Anil NEGI’,‘Anuk@GMAIL.COM’,‘Ludhiyana’,-2)

Error:

If I try to insert the valid value for the range it will successfully get inserted as shown below:

• Viewing created constraint:
  

Go to table click on Constraints folder as shown below:

To delete the constraint just right click on constraint and select delete

• Creating Check constraint using SQL
  

ALTER
TABLE
CUSTOMER

ADD
CONSTRAINT
CH_CUSTOMER_SIMCOUNT
CHECK (SIMCOUNT >=0 AND
SIMCOUNT
<=3)

• Drop Constraint using SQL
  

ALTER
TABLE
CUSTOMER

DROP
CONSTRAINT
CH_CUSTOMER_SIMCOUNT