How to Use CHECK Constraint in MySQL 8

CHECK Constraint MySQL 8Hello everyone, in this little post we will review a new feature in MySQL 8.

What is “CHECK Constraint”?

This is a new feature to specify a condition to check the value before INSERT or UPDATE into a row. The constraint could return an error if the result of a search condition is FALSE for any row of the table (but not if the result is UNKNOWN or TRUE).

This feature starts working on MySQL 8.0.16, and in previous versions, we could create it, but it doesn’t work, meaning the syntax is supported but it is not working,

There are some rules to keep in mind…

– AUTO_INCREMENT columns are not permitted
– Refer to another column in another table is not permitted
– Stored functions and user-defined functions are not permitted (you can not call a function or any user-defined functions)
– Stored procedure and function parameters are not permitted (you cannot call a procedure and function parameters)
– Subqueries are not permitted
– Columns used in foreign key for the next actions (ON UPDATE, ON DELETE) are not permitted
– This CHECK is evaluated for the next statements INSERT, UPDATE, REPLACE, LOAD DATA, and LOAD XML. Also, CHECK constraint is evaluated for INSERT IGNORE, UPDATE IGNORE, LOAD DATA … IGNORE, and LOAD XML … IGNORE. For those statements, a warning occurs if a constraint evaluates to FALSE. The insert or update is skipped.

Let’s See Some Examples

I created the next table to test this functionality. This is super easy as you can see in examples:

In this simple test, we can write or update rows only if the “age” column value is more than 15.

Let’s see an example trying to INSERT rows with the “age” column less than 15:

To DROP, use the next example:

Let’s see another example adding more logic into it. I altered the table with the next CHECKs:

We added more logic, and now it depends on the “gender” and “age” columns. A CHECK constraint is satisfied if, and only if, the specified condition evaluates to TRUE or UNKNOWN(for NULL column value) for the row of the table. The constraint is violated otherwise.

Let see an example from the previous logic.

As you can see in the ERROR message, MySQL is showing the CHECK constraint name. This is good to use from the application source code to debug the error and to know from which CHECK is failing.

Finally, this is the table structure:

We can add more logic in the table using this feature, but from my previous experience as a programmer, I don’t recommend adding logic in the tables because it is difficult to find or debug errors unless you do not have access to the application code.

Share this post

Leave a Reply