{"id":5284,"date":"2015-05-23T00:55:35","date_gmt":"2015-05-23T07:55:35","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5284"},"modified":"2023-12-29T21:02:50","modified_gmt":"2023-12-30T04:02:50","slug":"mysql-check-constraint","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-check-constraint\/","title":{"rendered":"MySQL CHECK Constraint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use MySQL CHECK<\/code> constraint to ensure that values stored in a column or group of columns satisfy a Boolean expression.<\/p>\n\n\n\n

MySQL 8.0.16 implemented the SQL check constraint. If you use MySQL with the earlier versions, you can emulate a CHECK<\/code> constraint<\/a> using a view<\/a> WITH CHECK OPTION<\/code> or a trigger<\/a>.<\/p>\n\n\n\n

Introduction to the MySQL CHECK constraint<\/h2>\n\n\n\n

Before MySQL 8.0.16, the CREATE TABLE<\/a><\/code> allows you to include a table CHECK<\/code> constraint. However, MySQL ignores all the CHECK<\/code> constraints:<\/p>\n\n\n

CHECK<\/span>(expression)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the  basic syntax:<\/p>\n\n\n
CONSTRAINT constraint_name \nCHECK<\/span> (expression) \n[ENFORCED<\/span> | NOT<\/span> ENFORCED<\/span>]<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
First, specify the name for the check constraint that you want to create after the CONSTRAINT<\/code> keyword. If you omit the constraint name, MySQL automatically generates a name with the following convention:<\/p>\n\n\n
table_name_chk_n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this convention, n<\/code> is an ordinal number such as 1,2, and 3. For example, the automatically generated names of CHECK<\/code> constraints of the parts<\/code> table will be parts_chk_1<\/code>, parts_chk_2<\/code>, and so on.<\/p>\n\n\n\n
Second, specify a Boolean expression<\/code> which must be evaluated to TRUE<\/code> or UNKNOWN<\/code> for each row of the table inside the parentheses after the CHECK<\/code> keyword. <\/p>\n\n\n\n
If the expression evaluates to FALSE<\/code>, the values violate the constraint or a constraint violation occurs.<\/p>\n\n\n\n
Note that MySQL treats 1 as true and 0 as false.<\/p>\n\n\n\n
Third, optionally specify the enforcement clause to indicate whether the check constraint is enforced:<\/p>\n\n\n\n
As of MySQL 8.0.16, the CREATE TABLE<\/a><\/code> supported essential features of table and column CHECK<\/code> constraints for all storage engines<\/a>.<\/p>\n\n\n\n