How to Use CHECK Constraint in MySQL 8

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

CREATE TABLE users (
id int not null auto_increment,
firstname varchar(50) not null,
lastname varchar(50) not null,
age TINYINT unsigned not null CONSTRAINT `check_1` CHECK (age > 15),
gender ENUM('M', 'F') not null,
primary key (id)
) engine = innodb;

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:

mysql> INSERT INTO users SET firstname = 'Name1', lastname = 'LastName1', age = 10, gender = 'M';
ERROR 3819 (HY000): Check constraint 'check_1' is violated.

To DROP, use the next example:

ALTER TABLE users DROP CHECK check_1;

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

ALTER TABLE users
ADD CONSTRAINT gender_male
CHECK (
    CASE
        WHEN gender = 'M'
        THEN
            CASE
                WHEN age >= 21
                THEN 1
                ELSE 0
            END
        ELSE 1
    END = 1
);

ALTER TABLE users
ADD CONSTRAINT gender_female
CHECK (
    CASE
        WHEN gender = 'F'
            THEN
                CASE
                    WHEN age >= 18
                    THEN 1
                    ELSE 0
                END
        ELSE 1
    END = 1
);

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.

mysql> INSERT INTO users SET firstname = 'Name2', lastname = 'LastName2', age = 10, gender = 'F';
ERROR 3819 (HY000): Check constraint 'gender_female' is violated.

mysql> INSERT INTO users SET firstname = 'Name3', lastname = 'LastName3', age = 10, gender = 'M';
ERROR 3819 (HY000): Check constraint 'gender_male' is violated.

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:

CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(50) NOT NULL,
`lastname` varchar(50) NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
`gender` enum('M','F') NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `gender_female` CHECK (((case when (`gender` = 'F') then (case when (`age` > 18) then 1 else 0 end) else 1 end) = 1)),
CONSTRAINT `gender_male` CHECK (((case when (`gender` = 'M') then (case when (`age` > 21) then 1 else 0 end) else 1 end) = 1))
) ENGINE=InnoDB AUTO_INCREMENT=4;

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.

by Walter Garcia via Percona Database Performance Blog