Check Constraint in SQL

Check constraint is validation or a rule which is applied on the column of a table.
When we apply check constraint to any column, it checks for specific values while inserting any record.
Check constraint can be created on a table level as well as column level.
Check constraints can be applied only to a single column, but there can be multiple check constraints in a single table.

Let us see a few practical examples to understand this concept more clearly. We will use the MariaDB database for writing all the queries.

1. Check constraint on column level:

To apply a check constraint on a column level, we must specify the check constraint just after the column name.

Syntax:

CREATE TABLE TableName(ColumnName1 Datatype(size), ColumnName2 Datatype(size), ColumnName3 Datatype(size) Constraint ConstraintName CHECK(ColumnName CONDITION Value),…, ColumnNameN Datatype(size));

We will write a query to create a table and specify a column-level check constraint on one of the columns:

CREATE TABLE employee(E_ID INT NOT NULL, Name VARCHAR(40), Salary INT, City VARCHAR(20), Designation VARCHAR(40), Date_of_Joining Date NOT NULL CHECK(Date_of_Joining > "2019-02-01"), Age INT, PRIMARY KEY(E_ID));

In the above query, we have specified the CHECK constraint on the Date_of_Joining column. According to this constraint, the Date_of_Joining column will allow only those records to be inserted where the date of joining is after '2019-02-01'.

To verify that the CHECK constraint is created on the Date_of_Joining column, we will execute the following query:

Now, we will try to insert a record in the employees table where the joining date of an employee is before '2019-02-01'.

INSERT INTO employees(E_ID, Name, Salary, City, Designation, Date_of_Joining, Age) VALUES(11, "Simran Khanna", 45500, "Kolhapur", "HR", "2019-01-01", 26);

Constraint failed error is issued when we tried to insert an employee whose joining date is '2019-01-01'. Since we have applied the check constraint on the Date_of_Joining column, it will allow only the joining date more than '2019-02-01'.

Example 2:

We will write a query to create a table and specify a column-level check constraint on more than one column.

Query:

CREATE TABLE students(S_ID INT NOT NULL, S_Name VARCHAR(40), Hometown VARCHAR(20), Percentage INT NOT NULL CHECK(Percentage >90), Favourite_Subject VARCHAR(20) NOT NULL CHECK( Favourite_Subject IN('Science', 'Maths', 'English')), PRIMARY KEY (S_ID));

In the above query, we have specified the CHECK constraint on the Percentage and Favourite_Subject column. According to this constraint, the Percentage column will allow only those records to be inserted where the percentage secured by students is above 90, and the favourite subject of the student is either Science, Maths or English.

To verify that the check constraint is created successfully on the Percentage and Favourite_Subject column, we will execute the following command:

Now, we will try to insert a record in the students table where the percentage secured by the student is below 90.

INSERT INTO students(S_ID, S_Name, Hometown, Percentage, Favourite_Subject) VALUES(106, "Harshada", "Nashik", 89, "Science");

Constraint failed error is issued when we tried to insert a student whose percentage is 89. Since we have applied the check constraint on the Percentage column, it will allow only the percentage more than 90.

Now, we will try to insert a record in the students table where the percentage secured by the student is above 90.

INSERT INTO students(S_ID, S_Name, Hometown, Percentage, Favourite_Subject) VALUES(106, "Harshada", "Nashik", 92, "Science");

A record where the percentage secured by the student is 92 is inserted successfully. Since we have applied the check constraint on the Percentage column, it will allow only the percentage more than 90.

1. Check constraint on table level:

To apply a check constraint on a table level, we must specify the check constraint before ending the table creation.

Syntax:

CREATE TABLE TableName(ColumnName1 Datatype(size), ColumnName2 Datatype(size), ColumnName3 Datatype(size),…, ColumnNameN Datatype(size), Constraint ConstraintName CHECK(ColumnName CONDITION Value));

Example 1:

We will write a query to create a table and specify a table-level check constraint on one of the columns.

CREATE TABLE employ(E_ID INT NOT NULL, Name VARCHAR(40), Salary INT, City VARCHAR(20), Designation VARCHAR(40), Date_of_Joining Date NOT NULL, Age INT, PRIMARY KEY(E_ID), CONSTRAINT Constraint_DOJ CHECK(Date_of_Joining <= "2019-02-01"));

To verify that the check constraint is created successfully on the Date_of_Joining column, we will execute the following command:

Now, we will try to insert a record in the employ table where the joining date of an employee is equal to '2019-02-01'.

INSERT INTO employ(E_ID, Name, Salary, City, Designation, Date_of_Joining, Age) VALUES(11, "Simran Khanna", 45500, "Kolhapur", "HR", "2019-02-01", 26);

A record where the joining date is less than or equal to '29-02-01' is inserted successfully. Since we have applied the check constraint on the Date_of_Joining column, it will allow only the dates less than or equal to '29-02-01'.

Example 2:

We will write a query to create a table and specify a table-level check constraint on more than one column.

CREATE TABLE student(S_ID INT NOT NULL, S_Name VARCHAR(40), Hometown VARCHAR(20), Percentage INT NOT NULL,  Favourite_Subject VARCHAR(20) NOT NULL, CONSTRAINT Constraint_Percentage (Percentage >90), CONSTRAINT Constraint_Fav_sub CHECK( Favourite_Subject IN('Science', 'Maths', 'English')), PRIMARY KEY (S_ID));

To verify that the check constraint is created successfully on the percentage and Favourite_Subject column, we will execute the following command:

Now, we will try to insert a record in the student table where the percentage secured by the student is above 90.

INSERT INTO students(S_ID, S_Name, Hometown, Percentage, Favourite_Subject) VALUES(106, "Harshada", "Nashik", 92, "Science");

Now, we will try to insert a record in the student table where the percentage secured by the student is below 90.

INSERT INTO student(S_ID, S_Name, Hometown, Percentage, Favourite_Subject) VALUES(106, "Harshada", "Nashik", 89, "Science");

3. Check constraint after table creation:

A situation may arise when we need to apply a check constraint on a column after the table creation. In such cases, we have to use the ALTER command to apply the check constraint on an already created table.

Syntax:

ALTER TABLE TableName ADD CONSTRAINT ConstraintName CHECK(ColumnName CONDITION Value);

Example 1:

Suppose we created an employee table without any constraints, and later we decided to add a constraint on one of the columns. Then we will execute the following query:

To verify that the check constraint is created successfully on the Age column, we will execute the following command:

Now, we will try to insert a record in the employee table where an employee's age is above 21.

INSERT INTO employees(E_ID, Name, Salary, City, Designation, Date_of_Joining, Age) VALUES(11, "Simran Khanna", 45500, "Kolhapur", "HR", "2019-02-02", 22);

Constraint failed error is issued when we tried to insert an employee whose age is above 21. Since we have applied the check constraint on the Age column, it will allow only the age values less than or equal to 21.

Example 2:

Suppose we created an employ table without any constraints, and later we decided to add a constraint on one of the columns. Then we will execute the following query:

ALTER TABLE employ ADD CONSTRAINT Constraint_Age CHECK (Date_of_Joining <= "2019-01-01");

To verify that the check constraint is created successfully on the Date_of_Joining column, we will execute the following command:

Now, we will try to insert a record in the employ table where the joining date of an employee is above '2019-01-01'.

INSERT INTO employ(E_ID, Name, Salary, City, Designation, Date_of_Joining, Age) VALUES(11, "Simran Khanna", 45500, "Kolhapur", "HR", "2019-02-02", 26);

Constraint failed error is issued when we tried to insert an employee whose joining date is '2019-02-02'. Since we have applied the check constraint on the Date_of_Joining column, it will allow only the joining dates below or equal to '2019-01-01'.

4. Remove a check constraint

Suppose we have a check constraint created on the table's column. Later, we decided to remove that constraint from the column. Then, in such a situation, we will use the ALTER command to remove the check constraint.

Syntax:

Example 1:

Suppose we have a check constraint assigned on one of the columns of the students table. Later, we decided to remove that constraint.

To verify the created constraint, we will execute the following query: