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:
We will write a query to create a table and specify a column-level check constraint on one of the columns:
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’.
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:
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.
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.
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:
Example 1:
We will write a query to create a table and specify a table-level check constraint on one of the columns.
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 joining date is less than or equal to ‘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’.
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.
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 student table where the percentage secured by the student is above 90.
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.
Now, we will try to insert a record in the student table where the percentage secured by the student is below 90.
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.
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:
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.
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:
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’.
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:
We will execute the following command to remove the constraint named Constraint_Fav_sub:
We will again execute the SHOW CREATE TABLE command to verify that the check constraint is removed successfully.
Example 2:
Suppose we have a check constraint assigned on one of the columns of the employee table. Later, we decided to remove that constraint.
To verify the created constraint, we will execute the following query:
We will execute the following command to remove the constraint named Constraint_DOJ:
We will again execute the SHOW CREATE TABLE command to verify that the check constraint is removed successfully.
