SQL Server - How to Apply Check Constraints?

Constraints are the rules and restrictions to apply on the type of data in a table. They specify the limit on the type of data that can be stored in a particular column in a table using constraints and also maintain the data integrity and accuracy in the table. They also ensure the unwanted data can't be inserted into tables.

The basic funda is that A Not null constraint restrict the insertion of null values into a column. 

Check Constraint - It is another feature of SQL which ensures that column value must be matched with some specific conditions and ensures that provided value passes all the Check constraints before storing it into data table otherwise the data for insertion will be rejected.

How can we create Check constraints?

In a table one or more column can contain a Check Constraint. We can create Check constraints at two levels -

• Column Level Check Constraints - We can define Check constraint with CREATE TABLE statement or in time with table definition. 

For example, the below query creates a table Employee with the column fields EmpId, FirstName, LastName and Age as NOT NULL but MobileNo column with Unique constraint. Thus, Age column has some specific check constraint to validate the data before inserting into the data table.

---- Column Level Unique Constraints CREATE TABLE TBL_EMPLOYEE ( EmpId int NOT NULL, FirstName varchar(25) NOT NULL, LastName varchar(25) NOT NULL, Age int NOT NULL CHECK(Age>=18), MobileNo Bigint Unique ) ---- Insert values into table INSERT INTO TBL_EMPLOYEE (EmpId, FirstName, LastName, Age, MobileNo) VALUE (101,’Ryan’,’Arjun’,22,7856124580)

Keep in your mind that if you are not going to assign a name for unique constraint and check constraint then system/SQL server will generate static constraint names for the unique and check constraint column. 

• Table Level Check Constraints -

You can create a Check constraint after creating the table with the help of Alter Table command as given below-

---- Table Level - Unique being added to another column ALTER TABLE TBL_EMPLOYEE ADD Age Int Constraint  CK_Emp_Age CHECK(Age>=18) ---- drop Check Constraint ALTER TABLE TBL_EMPLOYEE Drop Constraint  CK_Emp_Age

What happen, if you have some data into your table?

If you have some data into your table, for example in Age field you have value such as 16 and you want to apply check constraint on this Age column. Then you can create Table level Check constraint but you cannot avoid this value means you cannot say to your Age column that Age>=18 so on.

In this case, SQL will not allow this constraint to your column because this condition is not going to satisfy your existing data.

Demo - Class, please visit us at YouTube -