Implementation of check constraints in MySQL 8.0

Hello everyone, I am Tony, a teacher who only talks about techniques and doesn’t cut hair. This time we will introduce a new feature added in MySQL 8.0: check constraint (CHECK).

The check constraint in SQL is a type of integrity constraint, which can be used to constrain a field or some fields in a table to meet a certain condition. For example, the username must be capitalized and the balance cannot be less than zero.

Our common databases all implement check constraints, such as Oracle, SQL Server, PostgreSQL, and SQLite; however, MySQL has not really implemented this feature until the latest MySQL 8.0.16.

Before MySQL 8.0.15

In MySQL 8.0.15 and earlier, although the CREATE TABLE statement allows check constraint syntax of the form CHECK (expr), the clause is actually ignored after parsing. For example

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15 |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1
  -> (
  -> c1 INT CHECK (c1 > 10),
  -> c2 INT ,
  -> c3 INT CHECK (c3 < 100),
  -> CONSTRAINT c2_positive CHECK (c2 > 0),
  -> CHECK (c1 > c3)
  -> );
Query OK, 0 rows affected (0.33 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
    Table: t1
Create Table: CREATE TABLE `t1` (
 `c1` int(11) DEFAULT NULL,
 `c2` int(11) DEFAULT NULL,
 `c3` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Although we specified various CHECK options during definition, the final table structure does not contain any check constraints. This also means that we can insert illegal data:

mysql> insert into t1(c1, c2, c3) values(1, -1, 100);
Query OK, 1 row affected (0.06 sec)

If we want to implement a similar check constraint before MySQL 8.0.15, we can use triggers; or create a view with the WITH CHECK OPTION option and then insert or modify data through the view.

MySQL 8.0.16 and later

MySQL 8.0.16 was released on April 25, 2019, and finally brought the long-awaited CHECK constraint feature, which is valid for all storage engines. The CREATE TABLE statement allows the following forms of CHECK constraint syntax, which can specify both column-level constraints and table-level constraints:

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

The optional symbol parameter is used to assign a name to the constraint. If this option is omitted, MySQL will generate a name (table_name_chk_n) starting with the table name, plus _chk_ and a numeric number (1, 2, 3, ...). Constraint names have a maximum length of 64 characters and are case sensitive.

expr is a Boolean expression that specifies the condition of the constraint; each row of data in the table must satisfy expr to evaluate to TRUE or UNKNOWN (NULL). If the expression evaluates to FALSE, the constraint is violated.

The optional ENFORCED clause specifies whether the constraint should be enforced:

• If ENFORCED is omitted or specified, the constraint is created and enforced;
• If NOT ENFORCED is specified, the constraint is created but not enforced. This also means that the constraints will not take effect.

CHECK constraints can be specified at the column level or at the table level.

Column-level check constraints

Column-level constraints can only appear after a field definition and can only be constrained for that field. For example:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.16 |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1
  -> (
  -> c1 INT CHECK (c1 > 10),
  -> c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
  -> c3 INT CHECK (c3 < 100)
  -> );
Query OK, 0 rows affected (0.04 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
    Table: t1
Create Table: CREATE TABLE `t1` (
 `c1` int DEFAULT NULL,
 `c2` int DEFAULT NULL,
 `c3` int DEFAULT NULL,
 CONSTRAINT `c2_positive` CHECK ((`c2` > 0)),
 CONSTRAINT `t1_chk_1` CHECK ((`c1` > 10)),
 CONSTRAINT `t1_chk_2` CHECK ((`c3` < 100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

The check constraints on fields c1 and c3 use system-generated names; the check constraint on c2 uses a custom name.

All constraints in the SQL standard (primary key, unique constraint, foreign key, check constraint, etc.) belong to the same namespace, which means that they cannot have the same name. But in MySQL, each constraint type in the database belongs to its own namespace; therefore, a primary key and a check constraint can have the same name, but two check constraints cannot have the same name.

We insert a test data:

mysql> insert into t1(c1, c2, c3) values(1, -1, 100);
ERROR 3819 (HY000): Check constraint 'c2_positive' is violated.

All three fields of the inserted data violate the constraints. The result shows that c2_positive is violated. Because it is ranked first by name, it can be seen that MySQL checks the constraints in order by name.

Let's insert another test data:

mysql> insert into t1(c1, c2, c3) values(null, null, null);
Query OK, 1 row affected (0.00 sec)

The data is inserted successfully, so the NULL value does not violate the check constraint.

Table-level check constraints

Table-level constraints are independent of field definitions and can be applied to multiple fields, even before field definitions. For example:

mysql> drop table t1;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t1
  -> (
  -> CHECK (c1 <> c2),
  -> c1 INT,
  -> c2 INT,
  -> c3 INT,
  -> CONSTRAINT c1_nonzero CHECK (c1 <> 0),
  -> CHECK (c1 > c3)
  -> );
Query OK, 0 rows affected (0.04 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
    Table: t1
Create Table: CREATE TABLE `t1` (
 `c1` int DEFAULT NULL,
 `c2` int DEFAULT NULL,
 `c3` int DEFAULT NULL,
 CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),
 CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)),
 CONSTRAINT `t1_chk_2` CHECK ((`c1` > `c3`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

The first constraint, t1_chk_1, appears before the field definitions, but can still reference c1 and c2; the second constraint, c1_nonzero, uses a custom name; and the third constraint, t1_chk_2, appears after all field definitions.

We also insert some test data:

mysql> insert into t1(c1, c2, c3) values(1, 2, 3);
ERROR 3819 (HY000): Check constraint 't1_chk_2' is violated.

mysql> insert into t1(c1, c2, c3) values(null, 2, 3);
Query OK, 1 row affected (0.01 sec)

In the first record, c1 is less than c3, which violates the check constraint t1_chk_2. In the second record, c1 is NULL, and the result of the check constraint t1_chk_2 is UNKNOWN, which does not violate the constraint.

Mandatory options

Constraints created using the default mode or the ENFORCED option are in a mandatory check state. We can also modify them to NOT ENFORCED to ignore the check:

ALTER TABLE tbl_name
ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED

The modified check constraint still exists, but no checking is performed. For example:

mysql> alter table t1 
  -> alter check t1_chk_1 not enforced;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
    Table: t1
Create Table: CREATE TABLE `t1` (
 `c1` int DEFAULT NULL,
 `c2` int DEFAULT NULL,
 `c3` int DEFAULT NULL,
 CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),
 CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)) /*!80016 NOT ENFORCED */,
 CONSTRAINT `t1_chk_2` CHECK ((`c1` > `c3`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

From the latest definition, we can see that t1_chk_1 is in the NOT ENFORCED state. We insert a data that violates this constraint:

mysql> insert into t1(c1, c2, c3) values(1, 1, 0);
Query OK, 1 row affected (0.01 sec)

The c1 and c2 of this record are equal, but the insertion is successful.

If we need to migrate some historical data of lower versions, they may violate the new check constraint. In this case, we can disable the constraint first, and then enable the enforcement option again after the data is migrated and processed.

Check constraint limits

The CHECK condition expression in MySQL must meet the following rules, otherwise the check constraint cannot be created:

• Noncomputed and computed columns are allowed, but AUTO_INCREMENT fields or fields from other tables are not allowed.
• Literals, deterministic built-in functions (which produce the same result even if called multiple times by different users with the same input), and operators are allowed. Non-deterministic functions include: CONNECTION_ID(), CURRENT_USER(), NOW(), etc. They cannot be used to check constraints.
• Stored functions or custom functions are not allowed.
• Stored procedure and function parameters are not allowed.
• Variables are not allowed, including system variables, user-defined variables, and local variables of stored procedures.
• Subqueries are not allowed.

In addition, referential operations (ON UPDATE, ON DELETE) that define foreign key constraints on CHECK constraint fields are disabled; similarly, CHECK constraints are not allowed to be created on fields that have foreign key constraint referential operations.

For INSERT, UPDATE, REPLACE, LOAD DATA, and LOAD XML statements, errors are returned if check constraints are violated. At this point, the processing of the modified data depends on whether the storage engine supports transactions and whether strict SQL mode is used.

For INSERT IGNORE, UPDATE IGNORE, REPLACE, LOAD DATA ... IGNORE, and LOAD XML ... IGNORE statements, warnings are returned if check constraints are violated and the offending rows are skipped.

If the result type of the constraint expression is different from the data type of the column, MySQL will perform an implicit type conversion; if the type conversion fails or loses precision, an error will be returned.

Summarize

The new check constraints added in MySQL 8.0.16 improve MySQL's ability to implement business integrity constraints and make MySQL more compliant with SQL standards.

This is the end of this article about the implementation of check constraints, a new feature of MySQL 8.0. For more information about MySQL 8.0 check constraints, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!