Introduction
CHECK Constraint in SQL
CHECK constraint in SQL is used to check whether a value entered is valid or not according to the condition. We can say that the CHECK constraint in SQL is a type of integrity constraint that controls the value in a particular column. It ensures that the value inserted or updated in the column must be matched with given constraints. If the condition is evaluated as false, the record has not entered the table as the data violates the constraints.
EXAMPLES
Now, let's see some examples to understand CHECK constraint in SQL.
CHECK constraint in SQL with columns
The following example shows how to use CHECK constraint in SQL with columns.
The following statement creates a new table called EMPLOYEE and adds columns.
CREATE TABLE EMPLOYEE( EmpID INT PRIMARY KEY NOT NULL, Name VARCHAR (20) NOT NULL, Age INT NOT NULL CHECK (Age >= 18), Address VARCHAR (25) ); |
We have applied CHECK Constraint on Age, putting a condition to the values that can be inserted into the column of the table that only those values that are greater than or equal to 18, otherwise it violates the rule.
Now test this condition by inserting some values into the table
INSERT INTO EMPLOYEE( EmpID, Name, Age, Address) VALUES(1, ‘Aman’, 23, ‘Delhi’), (2, ‘Neha’, 19, ‘Pune’); |
Since the Age of Employee in both the entries is greater than or equal to 18. So these entries are valid and inserted into the table.
OUTPUT
The EMPLOYEE table will look like this:
EmpID |
Name |
Age |
Address |
1 |
Aman |
23 |
Delhi |
2 |
Neha |
19 |
Pune |
Now, if we try to run the following command:
INSERT INTO EMPLOYEE( EmpID, Name, Age, Address) VALUES(3, ‘Vivek’, 17, ‘Noida’); |
OUTPUT
This will show the error as the Age is not greater than or equal to 18 (as given in the constraints of the CHECK condition).
SQL CHECK on ALTER TABLE.
Now we will see SQL CHECK on ALTER TABLE.
We create a new employee table with the following command, without any check condition.
CREATE TABLE EMPLOYEE( EmpID INT PRIMARY KEY NOT NULL, Name VARCHAR (20) NOT NULL, Age INT NOT NULL, Address VARCHAR (25) ); |
Now the employee table is created, and we want to create a CHECK constraint on the Column 'Age'. We will run the following statement:
ALTER TABLE EMPLOYEE ADD CHECK (Age>= 18); |
We can also write this in the following manner, which supports naming the Check constraint in SQL.
ALTER TABLE EMPLOYEE ADD CONSTRAINT Chk_Employee CHECK (Age>=18); |
Again, we can test this condition by inserting some values into the table.
INSERT INTO EMPLOYEE( EmpID, Name, Age, Address) VALUES(1, ‘Aman’, 23, ‘Delhi’), (2, ‘Neha’, 19, ‘Pune’); |
Since the Age of Employee in both the entries is greater than or equal to 18. So these entries are valid and inserted into the table.
OUTPUT
The EMPLOYEE table will look like this:
EmpID |
Name |
Age |
Address |
1 |
Aman |
23 |
Delhi |
2 |
Neha |
19 |
Pune |
Now, if we try to run the following command:
INSERT INTO EMPLOYEE( EmpID, Name, Age, Address) VALUES(3, ‘Vivek’, 17, ‘Noida’); |
OUTPUT
This will show the error as the Age is not greater than or equal to 18 (as given in the constraints of the CHECK condition). The error will be like
ERROR 3819 (HY000): Check constraint ‘“constraint_name” is violated. |
Dropping the CHECK constraints
To drop the CHECK constraint in SQL, we can use the following command:
ALTER TABLE EMPLOYEE DROP CONSTRAINT constraint_name; Or ALTER TABLE EMPLOYEE DROP CHECK constraint_name; |
Generally, we use DROP CHECK constraint_name in MySQL.
The above statement will drop that CHECK condition, and now there will be no rule to insert values according to that CHECK condition. Thus the above examples explains how we can use CHECK constraint in SQL.
Must Read SQL Clauses
FAQs
-
What is SQL?
SQL( Structured Query Language) lets you access and manipulate the databases.
SQL can retrieve, create, update, insert and delete data from the database. SQL ( Structured Query Language) is the standard language for dealing with Relational Databases.
-
What are some rules to keep in mind while using CHECK constraint in SQL?
The following rules are to be followed:
Subqueries or nested queries are not permitted when using the CHECK constraint in SQL.
We can not refer to another column in another table using the CHECK constraint in SQL.
-
What is a Subquery?
A subquery is a query within another query. Subqueries are also known as inner queries or nested queries. They are the tool for performing the operations in multiple steps. Subqueries are embedded within the WHERE clause.