MySQL 8.0.16 introduces the SQL CHECK constraint feature. This is one of the most requested and long awaited features for MySQL. This post describes the details of the feature. Let’s get started!
Introduction
The CHECK constraint is a type of integrity constraint in SQL. The CHECK constraint specifies a search condition to check the value being entered into a row. The constraint is violated if the result of a search condition is FALSE for any row of the table (but not if result is UNKNOWN or TRUE).
In the older versions of MySQL, only a limited syntax to create CHECK constraints is supported, and the constraint is neither created nor evaluated: the constraint definition is ignored.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> CREATE TABLE t1 (c1 INTEGER CHECK (c1 > 0)); 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(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) -- CHECK constraint search condition is not evaluated. mysql> INSERT INTO t1 VALUES (0); Query OK, 1 row affected (0.00 sec) |
MySQL 8.0.16 introduces this missing integrity constraint feature.
How to create a CHECK constraint?
To create check constraint the SQL standard syntax
|
1 |
[ CONSTRAINT [symbol] ] CHECK ( condition) [ [ NOT ] ENFORCED ] |
is supported in the column definition and table definition of CREATE TABLE and ALTER TABLE statements.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> CREATE TABLE t1 (c1 INTEGER CONSTRAINT c1_chk CHECK (c1 > 0), -> c2 INTEGER, -> CONSTRAINT c2_chk CHECK (c2 > 0), -> CONSTRAINT c1_c2_chk CHECK (c1 + c2 < 9999)); Query OK, 0 rows affected (0.05 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, CONSTRAINT `c1_c2_chk` CHECK (((`c1` + `c2`) < 9999)), CONSTRAINT `c1_chk` CHECK ((`c1` > 0)), CONSTRAINT `c2_chk` CHECK ((`c2` > 0)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) |
SHOW CREATE TABLE lists all the check constraints in the table check constraints form.
Supplying a check constraint name is optional. If a constraint name is not supplied then MySQL generates a name for it.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
mysql> CREATE TABLE t1 (c1 INTEGER CONSTRAINT c1_chk CHECK (c1 > 0), -> c2 INTEGER CHECK (c2 > 0), -> c3 INTEGER, -> c4 INTEGER, -> CONSTRAINT c3_chk CHECK (c3 > 0), -> CHECK (c4 > 0), -> CONSTRAINT chk_all CHECK (c1 + c2 + c3 + c4 < 9999), -> CHECK (c1 + c3 < 5000)); Query OK, 0 rows affected (0.06 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, `c4` int(11) DEFAULT NULL, CONSTRAINT `c1_chk` CHECK ((`c1` > 0)), CONSTRAINT `c3_chk` CHECK ((`c3` > 0)), CONSTRAINT `chk_all` CHECK (((((`c1` + `c2`) + `c3`) + `c4`) < 9999)), CONSTRAINT `t1_chk_1` CHECK ((`c2` > 0)), CONSTRAINT `t1_chk_2` CHECK ((`c4` > 0)), CONSTRAINT `t1_chk_3` CHECK (((`c1` + `c3`) < 5000)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.02 sec) |
Here t1_chk_1, t1_chk_2, t1_chk_3 are generated names for check constraints.
All check constraints are enforced by default. If the user wants to create a check constraint but does not want to enforce it, then the “NOT ENFORCED” clause is used.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> CREATE TABLE t1 (c1 INTEGER CHECK (c1 > 0), -> c2 INTEGER CHECK (c2 > 0) NOT ENFORCED); 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(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, CONSTRAINT `t1_chk_1` CHECK ((`c1` > 0)), CONSTRAINT `t1_chk_2` CHECK ((`c2` > 0)) /*!80016 NOT ENFORCED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) |
How to add a check constraint to an existing table?
To add a check constraint to an existing table the following clause in ALTER TABLE statement is supported.
|
1
2
|
ALTER TABLE <table_name> ADD [CONSTRAINT [symbol]] CHECK (condition) [[NOT] ENFORCED] |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
mysql> CREATE TABLE t1 (c1 INTEGER, c2 INTEGER); 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(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) mysql> ALTER TABLE t1 ADD CHECK (c1 > 0), -> ADD CONSTRAINT c1_min_chk CHECK (c1 > 0), -> ADD CHECK (c2 > 0) NOT ENFORCED, -> ADD CONSTRAINT c2_min_chk CHECK (c2 > 0) NOT ENFORCED; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 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, CONSTRAINT `c1_min_chk` CHECK ((`c1` > 0)), CONSTRAINT `c2_min_chk` CHECK ((`c2` > 0)) /*!80016 NOT ENFORCED */, CONSTRAINT `t1_chk_1` CHECK ((`c1` > 0)), CONSTRAINT `t1_chk_2` CHECK ((`c2` > 0)) /*!80016 NOT ENFORCED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) |
How to alter a check constraint enforcement state?
To alter a check constraint enforcement state (enforced / not enforced) the following clause in ALTER TABLE table statement is supported.
|
1
2
|
ALTER TABLE <table_name> ALTER CHECK symbol [ NOT ] ENFORCED |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
mysql> CREATE TABLE t1 (c1 INTEGER CHECK (c1 > 0), -> c2 INTEGER CHECK (c2 > 0) NOT ENFORCED); Query OK, 0 rows affected (0.03 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, CONSTRAINT `t1_chk_1` CHECK ((`c1` > 0)), CONSTRAINT `t1_chk_2` CHECK ((`c2` > 0)) /*!80016 NOT ENFORCED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) mysql> ALTER TABLE t1 ALTER CHECK t1_chk_1 NOT ENFORCED; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE t1 ALTER CHECK t1_chk_2 ENFORCED; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 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, CONSTRAINT `t1_chk_1` CHECK ((`c1` > 0)) /*!80016 NOT ENFORCED */, CONSTRAINT `t1_chk_2` CHECK ((`c2` > 0)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) |
How to drop a check constraint?
To drop a check constraint the following clause in ALTER TABLE statement is supported.
|
1
2
|
ALTER TABLE <table_name> DROP CHECK symbol; |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
mysql> CREATE TABLE t1 (c1 INTEGER CHECK (c1 > 0), -> c2 INTEGER); 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(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, CONSTRAINT `t1_chk_1` CHECK ((`c1` > 0)), ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.02 sec) mysql> ALTER TABLE t1 DROP CHECK t1_chk_1; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) |
What is the effect of CHECK constraint on DMLs?
For DML operations INSERT, UPDATE, REPLACE, LOAD DATA, LOAD XML, the
constraints in the enforced state are evaluated. If a condition is evaluated to FALSE then an error is reported (with the IGNORE clause, a warning is reported and the offending row is skipped).
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
mysql> CREATE TABLE t1 (c1 INTEGER CHECK (c1 > 0), -> c2 INTEGER CHECK (c2 > 0) NOT ENFORCED); Query OK, 0 rows affected (0.04 sec) mysql> -- INSERT operation with valid values. t1_chk_1 condition is evaluated to TRUE. mysql> INSERT INTO t1 VALUES (1, 1); Query OK, 1 row affected (0.00 sec) mysql> -- INSERT operation with valid values. t1_chk_1 condition is evaluated to UNKNOWN for NULL value. mysql> INSERT INTO t1 VALUES (NULL, 1); Query OK, 1 row affected (0.00 sec) mysql> -- INSERT operation with valid values. Check constraint t1_chk_2 in "NOT ENFORCED" state is not evaluated. mysql> INSERT INTO t1 VALUES (1, 0); Query OK, 1 row affected (0.00 sec) mysql> -- Check constraint t1_chk_1 is evaluated to FALSE. mysql> INSERT INTO t1 VALUES (0, 1); ERROR 3820 (HY000): Check constraint 't1_chk_1' is violated. mysql> -- Check constraint t1_chk_1 is evaluated to FALSE. With IGNORE clause warning is reported and offending row is skipped. mysql> INSERT IGNORE INTO t1 VALUES (0, 1); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 3820 | Check constraint 't1_chk_1' is violated. | +---------+------+------------------------------------------+ 1 row in set (0.00 sec) |
Where to find information about all the defined CHECK constraints?
The INFORMATION_SCHEMA table “CHECK_CONSTRAINTS” provides information about all the check constraints defined.
|
1
2
3
4
5
6
7
8
9
10
|
mysql> CREATE TABLE t1 (c1 INTEGER CHECK (c1 > 0)); Query OK, 0 rows affected (0.03 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS; +--------------------+-------------------+-----------------+--------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE | +--------------------+-------------------+-----------------+--------------+ | def | test | t1_chk_1 | (`c1` > 0) | +--------------------+-------------------+-----------------+--------------+ 1 row in set (0.00 sec) |
In addition, the “INFORMATION_SCHEMA.TABLE_CONSTRAINTS” provides information about all the check constraints defined on the tables.
|
1
2
3
4
5
6
7
|
mysql> SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='t1'; +--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED | +--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+ | def | test | t1_chk_1 | test | t1 | CHECK | YES | +--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+ 1 row in set (0.02 sec) |
CHECK condition expression rules
CHECK condition expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.
- Non-generated and generated columns are permitted, except columns
with the AUTO_INCREMENT attribute. - Literals, deterministic built-in functions, and operators are
permitted. - Non-deterministic built-in functions (such as AVG, COUNT, RAND, LAST_INSERT_ID, FIRST_VALUE, LAST_VALUE, …) are not permitted.
- Sub-queries are not permitted.
- Environmental variables (such as CURRENT_USER, CURRENT_DATE, …) are not
permitted. - Variables (system variables, user-defined variables, and stored
program local variables) are not permitted. - Stored functions and user-defined functions are not permitted.
Please check out this new feature and let us know your feedback.
Thanks for using MySQL!