SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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;
Check Constraint in SQL

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');
Check Constraint in SQL

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;
Check Constraint in SQL

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;
Check Constraint in SQL

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');
Check Constraint in SQL

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;
Check Constraint in SQL

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;
Check Constraint in SQL

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');
Check Constraint in SQL

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;
Check Constraint in SQL

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;
Check Constraint in SQL

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');
Check Constraint in SQL

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;
Check Constraint in SQL

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;
Check Constraint in SQL

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');
Check Constraint in SQL

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;
Check Constraint in SQL

We will insert a record in the Employee table where the City of an employee is Jaipur.

SHOW CREATE TABLE EMPLOYEES;
Check Constraint in SQL

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;
Check Constraint in SQL

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;
Check Constraint in SQL

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;
Check Constraint in SQL

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;
Check Constraint in SQL



ADVERTISEMENT
ADVERTISEMENT