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));

Check Constraint in SQL

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:

SHOW CREATE TABLE employee;

Check Constraint in SQL

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);

Check Constraint in SQL

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));

Check Constraint in SQL

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:

SHOW CREATE TABLE students;

Check Constraint in SQL

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");

Check Constraint in SQL

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");

Check Constraint in SQL

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"));

Check Constraint in SQL

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

SHOW CREATE TABLE employ;

Check Constraint in SQL

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);

Check Constraint in SQL

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));

Check Constraint in SQL

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

SHOW CREATE TABLE student;

Check Constraint in SQL

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");

Check Constraint in SQL

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");

Check Constraint in SQL

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:

ALTER TABLE employee ADD CONSTRAINT Constraint_Age CHECK (Age <= 21);

Check Constraint in SQL

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

SHOW CREATE TABLE employee;

Check Constraint in SQL

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);

Check Constraint in SQL

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");

Check Constraint in SQL

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

SHOW CREATE TABLE employ;

Check Constraint in SQL

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);

Check Constraint in SQL

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:

ALTER TABLE TableName DROP CHECK ConstraintName;

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:

SHOW CREATE TABLE students;

Check Constraint in SQL

We will execute the following command to remove the constraint named Constraint_Fav_sub:

ALTER TABLE students DROP CHECK Constraint_Fav_sub;

Check Constraint in SQL

We will again execute the SHOW CREATE TABLE command to verify that the check constraint is removed successfully.

SHOW CREATE TABLE students;

Check Constraint in SQL

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:

SHOW CREATE TABLE employee;

Check Constraint in SQL

We will execute the following command to remove the constraint named Constraint_DOJ:

ALTER TABLE employee DROP CHECK Constraint_DOJ;

Check Constraint in SQL

We will again execute the SHOW CREATE TABLE command to verify that the check constraint is removed successfully.

SHOW CREATE TABLE employees;

Check Constraint in SQL

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz