EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL CHECK Constraint
 

MySQL CHECK Constraint

Aanchal Sharma
Article byAanchal Sharma
EDUCBA
Reviewed byRavi Rathore

Updated May 12, 2023

MySQL-CHECK-Constraint

 

 

Introduction to MySQL CHECK Constraint

A MySQL CHECK Constraint permits adding a specific range to the value in a table column. This constraint in MySQL defines some rules to restrict the values to be entered into a column. We can limit the range of values inserted within a table’s column 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 provides only specific values to a single table in a table.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

When you define CHECK Constraint on a column table, then with the CHECK keyword, we can range the value allowed in a column to insert. Users can enforce the fulfillment of a condition for table creation by restricting a column with a CHECK constraint.

Syntax

We define the CHECK constraint to a column at the time of table creation. We can define a CHECK constraint using the following syntax:

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. ALTER Table:

Users can create a CHECK constraint for a column in an already-created table 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. DROP Table:

Users can drop a CHECK constraint defined on a particular column in a table by using the following SQL syntax:

ALTER TABLE TableNameDROP CHECK CHK_check_name;
Note: Here, ‘Column_condition’ is the condition that will be provided for a column in the table to limit the values inserted during table creation or altering.

This ensures that the values in the table column or 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 to 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 just parsing the CHECK constraint and ignores it. The CREATE TABLE SQL syntax helps to include a CHECK constraint in the column to restrict specific values for entering the column.
  • MySQL CREATE TABLE syntax now supports specific essential structures for defining the CHECK constraint to a column for storage engines.
  • Also, if users omit or do not define a CHECK constraint, MySQL automatically generates a name for the constraint using the following convention:
TableName_chk_n
  • Where n defines an ordinal number 1,2,3,…. If we have a table named Persons, the auto-generated CHECK constraint will be persons_chk1, persons_chk2,…
  • As per the syntax, the condition applied for the table is to create a CHECK constraint for a column. Then, it must specify a Boolean result after evaluating either TRUE, UNKNOWN, or FALSE for every row in a table.
  • If the conditional expression returns FALSE, the CHECK constraint violation occurs for the values.
  • Using the term “table constraint” indicates that the constraint refers to multiple columns in the table. However, when the same term is used for a column, it only refers to that particular 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 the 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:

MySQL CHECK Constraint - 1

We have applied CHECK Constraint on Age, putting a condition to the values that can be inserted into the table column that only those values that satisfy 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:

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 following:

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:

MySQL CHECK Constraint - 2

You can view the constraint by the following command:

SHOW CREATE TABLE employee;

Output:

Constraint

Example #2 – MySQL CHECK Constraint on multiple columns

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, users join the conditions for two columns using the AND comparator.

Output:

AND comparator

SQL Statement:

SHOW CREATE TABLE parts;

Output:

MySQL CHECK Constraint - 5

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

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

Output:

CHECK constraint

Example #3 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 users execute this, the system displays a confirmation message asking whether to drop or not. Users must click OK to continue.

confirmation

MySQL CHECK Constraint - 8

Conclusion

Users declare MySQL CHECK Constraints during table creation. It can be used in two different levels, one is table level, and the other is column level. Similar to other constraints like PRIMARY key, FOREIGN Key, DEFAULT, UNIQUE, and NOT NULL that are defined for a column in the table, users utilize MySQL CHECK Constraints to safeguard the integrity of the table. CHECK constraints work by allowing only specific values that satisfy the CHECK conditional expression. It helps us to get only those values that are valid for the condition and our requirements.

Recommended Articles

We hope that this EDUCBA information on “MySQL CHECK Constraint” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW