Updated May 12, 2023
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.
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;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:
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:
You can view the constraint by the following command:
SHOW CREATE TABLE employee;Output:
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:
SQL Statement:
SHOW CREATE TABLE parts;Output:
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:
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.
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.
