{"id":3058,"date":"2019-05-14T09:05:30","date_gmt":"2019-05-14T08:05:30","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=3058"},"modified":"2019-05-14T09:05:37","modified_gmt":"2019-05-14T08:05:37","slug":"check-constraints-in-mysql","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2019\/05\/14\/check-constraints-in-mysql\/","title":{"rendered":"CHECK constraints in MySQL"},"content":{"rendered":"\n
\"Above<\/figure>\n\n\n\n

MySQL (really) supports CHECK CONSTRAINT<\/a> since version 8.0.16<\/a>.
In this article I will show you 2 things: <\/p>\n\n\n\n

  1. An elegant way to simulate<\/u> check constraint in MySQL 5.7 & 8.0.<\/li>
  2. How easy and convenient it is to use CHECK<\/strong> constraints starting from MySQL 8.0.16<\/strong>.<\/li><\/ol>\n\n\n\n

    Please note that this article is strongly inspired by Mablomy<\/a>‘s blog post: CHECK constraint for MySQL – NOT NULL on generated columns<\/a>.<\/p>\n\n\n\n

    I\u2019m using the optimized MySQL Server Docker images<\/a>, created, maintained and supported by the MySQL team at Oracle.
    For clarity I chose MySQL 8.0.15<\/strong> for the check constraint hack and obviously 8.0.16<\/strong> for the \u00ab\u00a0real\u00a0\u00bb check constraint implementation.<\/p>\n\n\n\n


    Deployment of MySQL 8.0.15 & MySQL 8.0.16:<\/p>\n\n\n\n

    $ docker run --name=mysql-8.0.15 -e MYSQL_ROOT_PASSWORD=unsafe -d mysql\/mysql-server:8.0.15\n d4ce35e429e08bbf46a02729e6667458e2ed90ce94e7622f1342ecb6c0dfa009<\/code><\/pre>\n\n\n\n
    $ docker run --name=mysql-8.0.16 -e MYSQL_ROOT_PASSWORD=unsafe -d mysql\/mysql-server:8.0.16\n d3b22dff1492fe6cb488a7f747e4709459974e79ae00b60eb0aee20546b68a0f<\/code><\/pre>\n\n\n\n
    Note<\/u>:<\/p>
    Obviously using a password on the command line interface can be insecure<\/strong>. <\/p>
    Please read the best practices of deploying MySQL on Linux with Docker<\/a>.<\/p><\/blockquote>\n\n\n\n\n\n
    Example 1<\/h2>\n\n\n\n
    Check constraints hack<\/h3>\n\n\n\n
    $ docker exec -it mysql-8.0.15 mysql -uroot -p --prompt='mysql-8.0.15> '\nEnter password: \n\nmysql-8.0.15> CREATE SCHEMA test;\nQuery OK, 1 row affected (0.03 sec)\n\nmysql-8.0.15> USE test\nDatabase changed\n\nmysql-8.0.15> SELECT VERSION();\n+-----------+\n| VERSION() |\n+-----------+\n| 8.0.15    |\n+-----------+\n\n\nmysql-8.0.15> \nCREATE TABLE checker_hack ( \n    i tinyint, \n    i_must_be_between_7_and_12 BOOLEAN \n         GENERATED ALWAYS AS (IF(i BETWEEN 7 AND 12, true, NULL)) \n         VIRTUAL NOT NULL\n);<\/code><\/pre>\n\n\n\n
    As you can see, the trick is to use Generated Columns<\/a>, available since MySQL 5.7 and the flow control operator IF<\/a> where the check condition is put.<\/p>\n\n\n\n
    mysql-8.0.15> INSERT INTO checker_hack (i) VALUES (11);\nQuery OK, 1 row affected (0.03 sec)\n\nmysql-8.0.15> INSERT INTO checker_hack (i) VALUES (12);\nQuery OK, 1 row affected (0.01 sec)\n\n\nmysql-8.0.15> SELECT i FROM checker_hack;\n+------+\n| i    |\n+------+\n|   11 |\n|   12 |\n+------+\n2 rows in set (0.00 sec)<\/code><\/pre>\n\n\n\n
    As expected, values that respect the condition (between 7 and 12) can be inserted.<\/p>\n\n\n\n
    mysql-8.0.15> INSERT INTO checker_hack (i) VALUES (13);\nERROR 1048 (23000): Column 'i_must_be_between_7_and_12' cannot be null\n\n\nmysql-8.0.15> SELECT i FROM checker_hack;\n+------+\n| i    |\n+------+\n|   11 |\n|   12 |\n+------+\n2 rows in set (0.00 sec)<\/code><\/pre>\n\n\n\n
    Outside the limits, an error is raised.
    We have our \u00ab\u00a0check constraint\u00a0\u00bb like feature \ud83d\ude42<\/p>\n\n\n\n\n\n
    Check constraint since MySQL 8.0.16<\/h3>\n\n\n\n
    $ docker exec -it mysql-8.0.16 mysql -uroot -p --prompt='mysql-8.0.16> '\nEnter password: \n\nmysql-8.0.16> CREATE SCHEMA test;\nQuery OK, 1 row affected (0.08 sec)\n\nmysql-8.0.16> USE test\nDatabase changed\n\nmysql-8.0.16> SELECT VERSION();\n+-----------+\n| VERSION() |\n+-----------+\n| 8.0.16    |\n+-----------+\n\n\nmysql-8.0.16> \nCREATE TABLE checker ( \n    i tinyint, \n    CONSTRAINT i_must_be_between_7_and_12 CHECK (i BETWEEN 7 AND 12 )\n);<\/code><\/pre>\n\n\n\n
    Since MySQL 8.0.16<\/strong>, the CHECK keyword do the job. 
    I would recommend to name wisely your constraint.
    The     syntax<\/a> is:<\/p>\n\n\n\n
    [CONSTRAINT [symbol]] CHECK<\/strong> (expr) [[NOT] ENFORCED]
    <\/p>\n\n\n\n
    From there, the following is rather obvious:<\/p>\n\n\n\n
    mysql-8.0.16> INSERT INTO checker (i) VALUES (11);\nQuery OK, 1 row affected (0.02 sec)\n\nmysql-8.0.16> INSERT INTO checker (i) VALUES (12);\nQuery OK, 1 row affected (0.03 sec)\n\n\nmysql-8.0.16> SELECT i FROM checker;\n+------+\n| i    |\n+------+\n|   11 |\n|   12 |\n+------+\n2 rows in set (0.00 sec)<\/code><\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    mysql-8.0.16> INSERT INTO checker (i) VALUES (13);\nERROR 3819 (HY000): Check constraint 'i_must_be_between_7_and_12' is violated.\n\n\nmysql-8.0.16> SELECT i FROM checker;\n+------+\n| i    |\n+------+\n|   11 |\n|   12 |\n+------+\n2 rows in set (0.00 sec)<\/code><\/pre>\n\n\n\n
    Easy! \ud83d\ude42<\/p>\n\n\n\n\n\n
    Example 2<\/h2>\n\n\n\n
    You can check a combination of columns.<\/p>\n\n\n\n
    Check constraints hack<\/h3>\n\n\n\n
    mysql-8.0.15> \nCREATE TABLE squares_hack (\n     dx DOUBLE, \n     dy DOUBLE, \n     area_must_be_larger_than_10 BOOLEAN \n           GENERATED ALWAYS AS (IF(dx*dy>10.0, true, NULL)) NOT NULL\n);\n\nmysql-8.0.15> INSERT INTO squares_hack (dx,dy) VALUES (7,4);\nQuery OK, 1 row affected (0.02 sec)\n\n\nmysql-8.0.15> INSERT INTO squares_hack (dx,dy) VALUES (2,4);\nERROR 1048 (23000): Column 'area_must_be_larger_than_10' cannot be null\n\n\nmysql-8.0.15> SELECT dx, dy FROM squares_hack;\n+------+------+\n| dx   | dy   |\n+------+------+\n|    7 |    4 |\n+------+------+\n1 row in set (0.00 sec)<\/code><\/pre>\n\n\n\n\n\n
    Check constraint since MySQL 8.0.16<\/h3>\n\n\n\n
    mysql-8.0.16> \nCREATE TABLE squares (\n     dx DOUBLE, \n     dy DOUBLE, \n     CONSTRAINT area_must_be_larger_than_10 CHECK ( dx * dy > 10.0 )\n);\n\n\nmysql-8.0.16> INSERT INTO squares (dx,dy) VALUES (7,4);\nQuery OK, 1 row affected (0.01 sec)\n\n\nmysql-8.0.16> INSERT INTO squares (dx,dy) VALUES (2,4);\nERROR 3819 (HY000): Check constraint 'area_must_be_larger_than_10' is violated.\n\n\nmysql-8.0.16> SELECT dx, dy FROM squares;\n+------+------+\n| dx   | dy   |\n+------+------+\n|    7 |    4 |\n+------+------+\n1 row in set (0.00 sec)<\/code><\/pre>\n\n\n\n
    Still easy!<\/p>\n\n\n\n\n\n
    Example 3<\/h2>\n\n\n\n
    You can also check text columns.<\/p>\n\n\n\n
    Check constraints hack<\/h3>\n\n\n\n
    mysql-8.0.15> \nCREATE TABLE animal_hack (  \n     name varchar(30) NOT NULL,  \n     class varchar(100) DEFAULT NULL,  \n     class_allow_Mammal_Reptile_Amphibian BOOLEAN \n           GENERATED ALWAYS AS (IF(class IN (\"Mammal\", \"Reptile\", \"Amphibian\"), true, NULL)) NOT NULL\n);  \n\nmysql-8.0.15> INSERT INTO animal_hack (name, class) VALUES (\"Agalychnis callidryas\",'Amphibian');  \nQuery OK, 1 row affected (0.02 sec)\n\nmysql-8.0.15> INSERT INTO animal_hack (name, class) VALUES (\"Orycteropus afer\", 'Mammal');  \nQuery OK, 1 row affected (0.02 sec)\n\nmysql-8.0.15> INSERT INTO animal_hack (name, class) VALUES (\"Lacerta agilis\", 'Reptile');  \nQuery OK, 1 row affected (0.02 sec)\n\n\nmysql-8.0.15> SELECT name, class FROM animal_hack;\n+-----------------------+-----------+\n| name                  | class     |\n+-----------------------+-----------+\n| Agalychnis callidryas | Amphibian |\n| Orycteropus afer      | Mammal    |\n| Lacerta agilis        | Reptile   |\n+-----------------------+-----------+\n3 rows in set (0.00 sec)<\/code><\/pre>\n\n\n\n
    mysql-8.0.15> INSERT INTO animal_hack (name, class) VALUES (\"Palystes castaneus\", 'Arachnid'); \nERROR 1048 (23000): Column 'class_allow_Mammal_Reptile_Amphibian' cannot be null\n\n\nmysql-8.0.15> SELECT name, class FROM animal_hack;\n+-----------------------+-----------+\n| name                  | class     |\n+-----------------------+-----------+\n| Agalychnis callidryas | Amphibian |\n| Orycteropus afer      | Mammal    |\n| Lacerta agilis        | Reptile   |\n+-----------------------+-----------+\n3 rows in set (0.00 sec)<\/code><\/pre>\n\n\n\n\n\n
    Check constraint since MySQL 8.0.16<\/h3>\n\n\n\n
    mysql-8.0.16> \nCREATE TABLE animal (  \n     name varchar(30) NOT NULL,  \n     class varchar(100) DEFAULT NULL,  \n     CONSTRAINT CHECK (class IN (\"Mammal\", \"Reptile\", \"Amphibian\"))\n);  \n\nmysql-8.0.16> INSERT INTO animal (name, class) VALUES (\"Agalychnis callidryas\",'Amphibian');  \nQuery OK, 1 row affected (0.04 sec)\n\nmysql-8.0.16> INSERT INTO animal (name, class) VALUES (\"Orycteropus afer\", 'Mammal');  \nQuery OK, 1 row affected (0.04 sec)\n\nmysql-8.0.16> INSERT INTO animal (name, class) VALUES (\"Lacerta agilis\", 'Reptile');  \nQuery OK, 1 row affected (0.04 sec)\n\n\nmysql-8.0.16> SELECT name, class FROM animal_hack;\n+-----------------------+-----------+\n| name                  | class     |\n+-----------------------+-----------+\n| Agalychnis callidryas | Amphibian |\n| Orycteropus afer      | Mammal    |\n| Lacerta agilis        | Reptile   |\n+-----------------------+-----------+\n3 rows in set (0.00 sec)<\/code><\/pre>\n\n\n\n
    mysql-8.0.16> INSERT INTO animal (name, class) VALUES (\"Palystes castaneus\", 'Arachnid');  \nERROR 3819 (HY000): Check constraint 'animal_chk_1' is violated.\n\n\nmysql-8.0.16> SELECT name, class FROM animal_hack;\n+-----------------------+-----------+\n| name                  | class     |\n+-----------------------+-----------+\n| Agalychnis callidryas | Amphibian |\n| Orycteropus afer      | Mammal    |\n| Lacerta agilis        | Reptile   |\n+-----------------------+-----------+\n3 rows in set (0.00 sec)<\/code><\/pre>\n\n\n\n
    Frankly easy!<\/p>\n\n\n\n
    I did not mention that the hack works as well in 8.0.16, though not needed anymore.<\/p>\n\n\n\n\n\n
    CHECK constraint is another useful feature implemented in MySQL (and not the last one, stay tuned!). 
    There are some other interesting things<\/a> to know about this feature but also about the others available in MySQL 8.0.16<\/a>. 
    Please have a look on the references below.<\/p>\n\n\n\n\n\n
    References<\/h2>\n\n\n\n