Everything you should know about improved MySQL 8.0.16 Check Constraint

MySQL 8.0.16 Introducing CHECK constraint

Finally, it has Arrived, The most waited and requested feature on SQL CHECK is included in the MySQL 8.0.16.

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 the 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.

How to create a CHECK constraint?

To create check constraint the SQL standard syntax

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

is supported in the column definition and table definition of CREATE TABLE and ALTER TABLE statements.

mysql> CREATE TABLE cyblance(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 cyblance\G
*************************** 1. row ***************************
   	Table: cyblance
Create Table: CREATE TABLE `cyblance` (
  `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.

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 cyblance\G
*************************** 1. row ***************************
   	Table: cyblance
Create Table: CREATE TABLE `cyblance` (
  `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 `cyblance_chk_1` CHECK ((`c2` > 0)),
  CONSTRAINT `cyblance_chk_2` CHECK ((`c4` > 0)),
  CONSTRAINT `cyblance_chk_3` CHECK (((`c1` + `c3`) < 5000))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)

Above, you can see the constraints generated for the cyblance_chk_1, cyblance_chk_2, cyblance_chk_3.

By default, All check constraints are enforced, the user can create the check constraints without enforcing by using “NOT ENFORCED” clause.

mysql> CREATE TABLE cyblance (c1 INTEGER CHECK (c1 > 0),
    ->                  c2 INTEGER CHECK (c2 > 0) NOT ENFORCED);
Query OK, 0 rows affected (0.04 sec)
 
mysql> SHOW CREATE TABLE cyblance\G
*************************** 1. row ***************************
   	Table: cyblance
Create Table: CREATE TABLE `cyblance` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  CONSTRAINT `cyblance_chk_1` CHECK ((`c1` > 0)),
  CONSTRAINT `cyblance_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?

The users can constraints to an existing table by using the following clause in ALTER TABLE statement is supported.

 	ALTER TABLE <cyblance>
       	ADD [CONSTRAINT [symbol]] CHECK (condition) [[NOT] ENFORCED]
mysql> CREATE TABLE cyblance (c1 INTEGER, c2 INTEGER);
Query OK, 0 rows affected (0.04 sec)
 
 
mysql> SHOW CREATE TABLE cyblance\G
*************************** 1. row ***************************
   	Table: cyblance
Create Table: CREATE TABLE `cyblance` (
  `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 Cyblance 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 cyblance\G
*************************** 1. row ***************************
   	Table: cyblance
Create Table: CREATE TABLE `cyblance` (
  `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 `cyblance_chk_1` CHECK ((`c1` > 0)),
  CONSTRAINT `cyblance_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.

 	ALTER TABLE <cyblance>
       	ALTER CHECK symbol [ NOT ] ENFORCED
mysql> CREATE TABLE cyblance (c1 INTEGER CHECK (c1 > 0),
    ->                  c2 INTEGER CHECK (c2 > 0) NOT ENFORCED);
Query OK, 0 rows affected (0.03 sec)
 
mysql> SHOW CREATE TABLE cyblance\G
*************************** 1. row ***************************
   	Table: cyblance
Create Table: CREATE TABLE `cyblance` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  CONSTRAINT `cyblance_chk_1` CHECK ((`c1` > 0)),
  CONSTRAINT `cyblance_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 cyblance ALTER CHECK cyblance_chk_1 NOT ENFORCED;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql>  ALTER TABLE cyblance ALTER CHECK cyblance_chk_2 ENFORCED;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> SHOW CREATE TABLE cyblance\G
*************************** 1. row ***************************
   	Table: cyblance
Create Table: CREATE TABLE `cyblance` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  CONSTRAINT `cyblance_chk_1` CHECK ((`c1` > 0)) /*!80016 NOT ENFORCED */,
  CONSTRAINT `cyblance_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.

      ALTER TABLE <cyblance>
            DROP CHECK symbol;
 
mysql> CREATE TABLE cyblance (c1 INTEGER CHECK (c1 > 0),
    ->                  c2 INTEGER);
Query OK, 0 rows affected (0.04 sec)
 
mysql> SHOW CREATE TABLE cyblance\G
*************************** 1. row ***************************
   	Table: cyblance
Create Table: CREATE TABLE `cyblance` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  CONSTRAINT `cyblance_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 cyblance DROP CHECK cyblance_chk_1;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> SHOW CREATE TABLE cyblance\G
*************************** 1. row ***************************
   	Table: cyblance
Create Table: CREATE TABLE `cyblance` (
  `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.

CHECK condition expression rules

CHECK condition expressions must adhere to the following rules. An error will occur if expression will have refused 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.