EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL CHECK Constraint

MySQL CHECK Constraint

Aanchal Sharma
Article byAanchal Sharma
Priya Pedamkar
Reviewed byPriya Pedamkar

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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
C++ PROGRAMMING Course Bundle - 9 Courses in 1 | 5 Mock Tests
37+ Hour of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Course Bundle - 28 Courses in 1 | 5 Mock Tests
123+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL Course Bundle - 51 Courses in 1 | 6 Mock Tests
204+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Course Bundle - 13 Courses in 1
53+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Course Bundle - 18 Courses in 1 | 3 Mock Tests
 93+ Hour of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more