MySQL CHECK Constraint

Introduction to MySQL CHECK Constraint

A MySQL CHECK Constraint permits to add a certain range to the value in a column of the table. Actually, this is a constraint in MySQL as it defines some rules to restrict the values to be entered into a column. We can limit the range of values to be inserted within a column of a table in the database. Basically, a MySQL Constraint helps to check what type of values are to be stored in a table’s column. This is the major purpose of encouraging MySQL CHECK Constraint to maintain database integrity. This MySQL CHECK constraint works to provide only certain values to a single table in a table.

When you define CHECK Constraint on a column table, then with the CHECK keyword we can range the value that can be allowed in a column to insert. This type of column, when restricted with CHECK constraint, ensures that the condition must be fulfilled for creating a table.

Syntax

We define CHECK constraint to a column at the time of table creation. The syntax to define CHECK Constraint can be written as below:

1. For MySQLCHECK Constraint on CREATE Table:

CREATE TABLE TableName( Column1  INT  NOT NULL, Column2 INT  NOT NULL CHECK (Column2_condtion), Column3 Varchar(255),…… ,
PRIMARY KEY (Column1)
ADD CONSTRAINT myCheckConstraint CHECK(Column2_condition));

2. To allow the use of CHECK constraint on multiple columns in a table, you can implement the following query:

CREATE TABLE TableName( Column1  INT  NOT NULL, Column2 INT  NOT NULL CHECK (Column2 _condition), Column3 Varchar(255),…… ,
PRIMARY KEY (Column1)
ADD CONSTRAINT check_nameCheckConstraint CHECK(Column2 _condition AND Column3_condition) );

3. For MySQL CHECK Constraint on ALTER Table:

If the table is already created then, we can alter the column to create a CHECK Constraint by using the following SQL query syntax:

ALTER TABLE TableNameADD CHECK (Column_condition);

4. For naming a CHECK Constraint and defining it on more than one column, we have to use the following syntax format in MySQL:

ALTER TABLE TableName ADD CONSTRAINT CHECK CHK_check_name CHECK (CHECK_condition);

5. For MySQL CHECK Constraint on DROP Table:

If we want to drop the CHECK constraint that is defined on a certain column in a table, thenwe apply the following SQL syntax:

ALTER TABLE TableNameDROP CHECK CHK_check_name;

This ensures that the values in the table column or in a group of columns inserted have passed the condition and satisfied the Boolean expression.

6. We can view the CHECK constraint created in the table’s column through the below SQL command:

SHOW CREATE Table TableName;

Here, we will receive the output where we can view the column CHECK constraint. We will explain in the examples section in a better way.

How does CHECK Constraint work in MySQL?

• In MySQL, the CREATE TABLE permits to use the CHECK constraint. However, it allows to just parsing the CHECK constraint and ignores it. The CREATE TABLE SQL syntax helps to include a CHECK constraintin the column to restrict certain values for entering the column.
• MySQL CREATE TABLE syntax now supports certain essential structuresfor defining the CHECK constraint to a column for storage engines.
• Also, if CHECK constraint is omitted, not defined then MySQL produces the name having the convention below automatically:

TableName_chk_n

• Where n defines an ordinal number 1,2,3,…. If we have a table named as Persons then, the auto generated CHECK constraint will be persons_chk1, persons_chk2,…
• As per the syntax, the condition applied for the table to create a CHECK constraintfor a column and then it must specify a Boolean result after evaluation either TRUE or UNKNOWN or FALSE for every row in a table.
• If then conditional expression returns FALSE then, the CHECK constraint violation occurs for the values.
• If we use table Constraint, it indicates that it refers to multiple columns in the table but when it is used for a column then it refers to only that column.

Examples to Implement CHECK Constraint in MySQL

Let us describe the CHECK constraint with the following examples:

Example #1 – MySQL CHECK Constraint on a column

We are creating a CHECK Constraint on a column ‘Age’ while creating a table named‘Employee’:

Code:

CREATE TABLE Employee(EmpIDINT NOT NULL, Name VARCHAR (20) NOT NULL,
Age INT NOT NULL CHECK (AGE >= 15),City Varchar (25),Profile Varchar (255),
PRIMARY KEY (EmpID)ADD CONSTRAINT testCheckConstraint CHECK(AGE >= 15));

Output:

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 satisfies to be greater than or equal to 15, otherwise it violates the rule.

Let us test the condition by inserting such a value in the column with the following INSERT query statements:

All in One Data Science Bundle(360+ Courses, 50+ projects)

Price
View Courses

360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (82,976 ratings)

If we execute this

INSERT INTO `employee`(`EmpID`, `Name`, `Age`, `City`, `Profile`) VALUES
('101','Nikhil','18','Delhi', 'Engineer');

Then, the row will be inserted into the table but we will implement the below:

INSERT INTO `employee`(`EmpID`, `Name`, `Age`, `City`, `Profile`) VALUES
('101','Nikhil','10','Delhi', 'Engineer');

Then, we will receive the below MySQL error to violate the rule of CHECK constraint:

Output:

You can view the Constraint by the following command:

SHOW CREATE TABLE employee;

Output:

Example #2 – MySQL CHECK Constraint on multiple column.s

SQL statement is as follows:

Code:

CREATE TABLE Travel (TID int NOT NULL, Name varchar (255) NOT NULL, City
varchar(255), Age int,  CONSTRAINT CHK_Travel CHECK (Age>=15 AND
City='Delhi') );

Here, the conditions for two columns are joined by AND comparator

Output:

SQL Statement:

SHOW CREATE TABLE parts;

Output:

If we insert this then, it will not satisfy the CHECK constraint and show error.

INSERT INTO `travel`(`TID`, `Name`, `City`, `Age`)
VALUES ('201','Divya','Jaipur','10');

Output:

Example #3 – MySQL CHECK Constraint ALTER & DROP

We can use the below query to alter the table to drop the constraint created to a column:

Code:

ALTER TABLE travel DROP CHECK CHK_Travel;
Or,
ALTER TABLE travel DROP CONSTRAINT CHK_Travel;

When you execute this, a confirmation is displayed either sure to drop or not, then click OK to continue.

Output:

Conclusion

MySQL CHECK Constraint is declared during table creation. It can be used in two different levels, one is table level and the other is the column level. It is used to protect the integrity of the table as similar to other Constraint like PRIMARY key, FOREIGN Key, DEFAULT, UNIQUE and NOT NULL defined to a column in the table. CHECK constraint works by allowing certain values that are satisfied by the CHECK conditional expression. It helps us to get only those values that are valid for the condition and our requirements.

Recommended Articles

This is a guide to MySQL CHECK Constraint. Here we discuss an introduction to MySQL CHECK Constraint along with syntax and examples to understand in better way. You can also go through our other related articles to learn more –

1. MySQL Subquery
2. Unique Key in MySQL
3. ANY in MySQL
4. MIN() in MySQL