How to Use CHECK Constraints in MySQL 8

MySQL 8 (starting with version 8.0.16) introduces support for CHECK constraints, allowing you to define conditions that rows must satisfy before they are inserted or updated. If the condition evaluates to FALSE, the operation fails; UNKNOWN or TRUE values are accepted.

Usage restrictions to keep in mind:

AUTO_INCREMENT columns cannot be used in a CHECK constraint.

References to columns in other tables are not allowed.

Stored functions and user‑defined functions cannot be used.

Stored procedure and function parameters are prohibited.

Subqueries are not permitted.

Columns used in foreign‑key actions (ON UPDATE, ON DELETE) cannot be part of a CHECK.

The constraint is evaluated for INSERT, UPDATE, REPLACE, LOAD DATA, and LOAD XML statements (including their IGNORE variants); a FALSE result generates a warning and skips the row.

Below is a simple example that creates a table with an age check:

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;

With this table, only rows where age > 15 can be inserted or updated. Attempting to insert a row with age < 15 results in an error:

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

You can drop the constraint with:

ALTER TABLE users DROP CHECK check_1;

For more complex validation, you can add additional CHECK constraints that use CASE expressions. The following adds gender‑specific age rules:

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);

Now the table enforces that male users must be at least 21 years old and female users at least 18 years old. Inserting rows that violate these rules produces errors such as:

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.

The final table definition looks like this:

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;

While CHECK constraints can add useful validation logic directly in the database, the author advises caution: embedding complex logic in tables can make debugging harder unless you have no access to application code.

Keywords: #new feature# #check constraint# #mysql8#