Check Constraint in SQL
The Check Constraint in SQL is the rule or set of rules used to limit the data range that can be entered in a table column. Check constraint is used on the table as well as on the column. So we can say, Check constraint can be created on the Column level and Table Level.
Check Constraint applied on the column, and it will allow certain values for this column while inserting into the column. If you define a CHECK constraint on a table, it can limit the values in certain columns based on values in other columns in the row. We can apply multiple Check Constraints in a single table.
Let's see certain examples to understand the Check Constraint concept more clearly.
Column Level Check Constraint
The check constraint is defined just after the column name, known to be Column Level Check Constraint. Syntax of Column Level Check Constraint as follows,
CREATE TABLE TABLE_NAME(COLUMN_NAME1 DATATYPE(SIZE), COLUMN_NAME2 DATATYPE(SIZE) CHECK(COLUMN_NAME CONDITION), COLUMN_NAME3 DATATYPE(SIZE));For example, we will create a table and define a column-level check constraint on one of the following columns in a table:
CREATE TABLE EMPLOYEES (EMPLOYEEID INT PRIMARY KEY, EMPLOYEE_NAME VARCHAR(50) NOT NULL, SALARY INT CHECK(SALARY > 40000), CITY VARCHAR(20) NOT NULL, DEPARTMENT VARCHAR(30) NOT NULL);In the above query, we have created a table named Employees and defined certain columns. In the table Employee, we have specified check constraints on the salary column. The constraint itself says that the salary column will accept those data only whose employee salary is greater than 40000; if the salary is less than 40000, constraint violation errors will occur.
To cross-check that the CHECK constraint is defined on the Salary column, we will use the below query:
SHOW CREATE TABLE EMPLOYEES;
We will insert a record in the Employee table where an employee's salary is less than 40000.
INSERT INTO EMPLOYEES VALUES (1171101, 'Parag Chordia', 38000, 'Pune', 'Java');
When we inserted an employee record whose salary is less than 40000, the constraint failed error message is displayed; why? Because we applied Check constraint on a salary which will allow only those records whose employee salary are greater than 40000.
We will insert a record in the Employee table where an employee's salary is greater than 40000.
INSERT INTO EMPLOYEES VALUES (1171101, 'Parag Chordia', 45000, 'Pune', 'Java');To Cross check whether data is inserted in a table, we will use the below query:
SELECT * FROM EMPLOYEES;
An employee record is successfully inserted into the table as we entered Employee Salary greater than 40000.
Example 2: We will create a table and define a column-level check constraint on more than one column in a table.
CREATE TABLE MANAGER(MANAGERID INT PRIMARY KEY, NAME VARCHAR(40) NOT NULL, SALARY INT CHECK(SALARY>=60000), DEPARTMENT VARCHAR(20) NOT NULL CHECK(DEPARTMENT IN('Oracle', 'FMW', 'Testing', 'Java' )));We have created a table name Manager in the above query and defined certain columns. We have specified check constraints on the salary column in a table Manager. The constraint itself says that the salary column will accept those data only whose manager salary is greater than 60000; if the salary is less than 60000, constraint violation errors will occur, and the manager's Department is Oracle, FMW, Testing, and Java.
To cross-check that the CHECK constraint is defined on the Salary column and Department Column, we will use the below query:
SHOW CREATE TABLE MANAGER;
We will insert a record in the Manager table where the salary of a manager is less than 60000 and the Department is Java.
INSERT INTO MANAGER VALUES(1, 'Bhavesh Bardiya', 59500, 'Java');
When we inserted a manager record whose salary is less than 60000, the constraint failed error message is displayed; why? Because we applied Check constraint on a salary which will allow only those records whose manager salary are greater than 60000. But no constraint error occurred on the Department column because we inserted those values which column allowed to be inserted,
We will insert a record in the Manager table where the salary of a manager is greater than 60000, and the Department is Java.
INSERT INTO MANAGER VALUES(1, 'Bhavesh Bardiya', 62000, 'Java');To Cross check whether data is inserted in a table, we will use the below query:
SELECT * FROM MANAGER;
The manager record is successfully inserted into the table as we entered Manager Salary greater than 60000 and Department name Java.
Table Level Check Constraint
The check constraint is defined at the end of the table, known as the Table Level Check Constraint. Syntax of Table Level Check Constraint as follows,
CREATE TABLE TABLE_NAME(COLUMN_NAME1 DATATYPE(SIZE), COLUMN_NAME2 DATATYPE(SIZE), COLUMN_NAME3 DATATYPE(SIZE), CONSTRAINT CONSTAINT_NAME CHECK(COLUMN_NAME CONDITION));Example 1: We will create a table and define a Table level check constraint on one of the following columns in a table.
CREATE TABLE EMPLOYEES (EMPLOYEEID INT PRIMARY KEY, EMPLOYEE_NAME VARCHAR(50) NOT NULL, SALARY INT, CITY VARCHAR(20) NOT NULL, DEPARTMENT VARCHAR(30) NOT NULL, CONSTRAINT salary_constraint CHECK(SALARY > 40000));In the above query, we have created a table named Employees and defined certain columns. In the table Employee, we have specified check constraints on the salary column. The constraint itself says that the salary column will accept those data only whose employee salary is greater than 40000; if the salary is less than 40000 constraints, violation errors will occur.
To cross-check that the CHECK constraint is defined on the Salary column, we will use the below query:
SHOW CREATE TABLE EMPLOYEES;
We will insert a record in the Employee table where an employee's salary is less than 40000.
INSERT INTO EMPLOYEES VALUES (1171101, 'Naman Sharma', 38000, 'Pune', 'Java');
When we inserted an employee record whose salary is less than 40000, the constraint failed error message is displayed; why? Because we applied Check constraint on a salary which will allow only those records whose employee salary are greater than 40000.
We will insert a record in the Employee table where an employee's salary is greater than 40000.
INSERT INTO EMPLOYEES VALUES (1171101, 'Naman Sharma', 45000, 'Pune', 'Java');To Cross check whether data is inserted in a table, we will use the below query:
SELECT * FROM EMPLOYEES;
An employee record is successfully inserted into the table as we entered Employee Salary greater than 40000.
Example 2: We will create a table and define a column-level check constraint on more than one column in a table.
CREATE TABLE MANAGER (MANAGERID INT PRIMARY KEY, NAME VARCHAR(40) NOT NULL, SALARY INT, DEPARTMENT VARCHAR(40) NOT NULL, CONSTRAINT SALARY_CONST CHECK(SALARY>60000), CONSTRAINT DEPT_CONST CHECK(DEPARTMENT IN(‘'Oracle', 'FMW', ‘Java’, 'Testing')));We have created a table name Manager in the above query and defined certain columns. We have specified check constraints on the salary column in a table Manager. The constraint itself says that the salary column will accept those data only whose manager salary is greater than 60000; if the salary is less than 60000, constraint violation errors will occur, and the manager's Department is Oracle, FMW, Testing, and Java.
To cross-check that the CHECK constraint is defined on the Salary column and Department Column, we will use the below query:
SHOW CREATE TABLE MANAGER;
We will insert a record in the Manager table where the salary of a manager is less than 60000 and the Department is Java.
INSERT INTO MANAGER VALUES(1, 'Surili Jain', 59500, 'Java');
When we inserted a manager record whose salary is less than 60000, the constraint failed error message is displayed; why? Because we applied Check constraint on a salary which will allow only those records whose manager salary are greater than 60000. But no constraint error occurred on the Department column because we inserted those values which column allowed to be inserted,
We will insert a record in the Manager table where the salary of a manager is greater than 60000, and the Department is Java.
INSERT INTO MANAGER VALUES(1, 'Surili Jain', 62500, 'Java');To Cross check whether data is inserted in a table, we will use the below query:
SELECT * FROM MANAGER;
The manager record is successfully inserted into the table as we entered Manager Salary greater than 60000 and Department name Java.
Check Constraint Using Alter
We created a table and forgot to add CHECK CONSTRAINT while creating a table, and then we need to add CHECK CONSTRAINT in a table. In such cases, we will use ALTER command to apply the CHECK CONSTRAINT on the existing table.
Syntax of Check Constraint using Alter as follows,
ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME CHECK (COLUMN_NAME CONDITION);Example 1: Suppose we created an Employees table without adding Check Constraints. Now we want to add Check Constraint on one of the columns. Then we will use the below query:
ALTER TABLE EMPLOYEES ADD CONSTRAINT Sal_Constraint CHECK (SALARY > 35000); To cross-check that the CHECK constraint is defined on the Salary column, we will use the below query:
SHOW CREATE TABLE EMPLOYEES;
We will insert a record in the Employee table where an employee's salary is less than 35000.
INSERT INTO EMPLOYEES VALUES (1001, 'Abhinav Patil', 30000, 'Mumbai', 'Testing');
When we inserted an employee record whose salary is less than 35000, the constraint failed error message is displayed; why? Because we applied Check constraint on a salary which will allow only those records whose employee salary are greater than 35000.
Example 2: Suppose we created an Employees table without adding Check Constraints. Now we want to add Check Constraint on one of the columns. Then we will use the below query:
ALTER TABLE EMPLOYEES ADD CONSTRAINT City_Constraint CHECK (CITY IN ('Mumbai', 'Pune', 'Bangalore', 'Chennai'));To cross-check that the CHECK constraint is defined on the City column, we will use the below query:
SHOW CREATE TABLE EMPLOYEES;
We will insert a record in the Employee table where the City of an employee is Jaipur.
SHOW CREATE TABLE EMPLOYEES;
When we inserted an employee record whose city name is Jaipur, the constraint failed error message is displayed; why? Because we applied Check constraint on City which will allow only those records where city name will be 'Mumbai', 'Pune', 'Bangalore', or 'Chennai’.
Drop Check Constraint
Suppose we have defined a CHECK CONSTRAINT on the table columns. Later we want to delete that CONSTRAINT from the column. Then we will use ALTER command to drop the CHECK CONSTRAINT.
Syntax of Drop Check Constraint as follows,
ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME;Example 1: Suppose we have defined Check Constraint on one of the columns of the Employees table. Later, we decided to drop that constraint.
We will first check created constraint, for this use following query:
SHOW CREATE TABLE EMPLOYEES;
We will write the below query to drop constraint named 'City_Constraint’.
ALTER TABLE EMPLOYEES DROP CONSTRAINT City_Constraint;We will again use the SHOW CREATE TABLE query to check that constraint is dropped successfully.
SHOW CREATE TABLE EMPLOYEES;
Example 2: Suppose we have defined Check Constraint on one of the columns of the Manager Table. Later, we decided to drop that constraint.
We will first check created constraint, for this use following query:
SHOW CREATE TABLE MANAGER;
We will write the below query to drop constraint named 'SALARY_CONST'.
ALTER TABLE MANAGER DROP CONSTRAINT SALARY_CONST;We will again use the SHOW CREATE TABLE query to check that constraint is dropped successfully.
SHOW CREATE TABLE MANAGER;