MySQL8.0.16で追加されたCHECK制約を試してみる。

PHP Conference Japan 2019 で @yoku0825 さんのセッション「20年前のMySQL、今のMySQL」を拝聴した際に、MySQL8.0 の機能の紹介でCHECK制約が出てきたので、実際に動かして試してみます。

Contents

1. check制約
2. 実行環境
3. テーブルを作成しCHECK制約を作成する
4. 動作確認
5. 制約名を設ける
6. CHECK制約を外す
7. あとからCHECK制約を付与する
8. 制約の強制/非強制で切り替える

check制約

CHECK制約は、SQLの整合性制約の一種です。CHECK制約は、行に入力される値をチェックするための検索条件を指定します。テーブルの行の検索条件の結果がFALSEの場合（結果がUNKNOWNまたはTRUEの場合は除く）、制約に違反します。

以下はwikipediaから

CHECK制約 （-せいやく、Check Constraint）とは、データベースにおいてデータを追加、更新する際の有効なデータを定義する規則のことをいう。チェック制約はテーブルの１つの列に複数つけることができ、条件に合う（True）かもしくは合わない(False)のいずれかの値を取る式でなければならない。

wikipedia - check制約

つまり、レコード挿入や更新時に、あるカラムに対して、どういう値なら入力を受け付けるかを式で定義できる。というもの。

int でも 10 以上だけOKとする。とか、varchar でも１０文字以上だけOKとする、みたいな感じ。

MySQL 8.0 Reference Manual - CHECK Constraints
https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

check制約自体は MySQL 8.0.16 でリリース（2019-04-25）されたもの。

実行環境

MySQL 8.0.16 だけあれば良いので、Dockerでサクッと構築します。 （ちょっとあまのじゃくしてdocker-compose使っています）

docker-compose.yml

version: '3'
services:
  mysql:
    image: mysql:8.0.16
    container_name: sample_mysql_8_0_16
    command: mysqld --default-authentication-plugin=mysql_native_password
    environment:
      - MYSQL_DATABASE=sample
      - MYSQL_ROOT_PASSWORD=root
    volumes:
      - ./mysql/:/etc/mysql/conf.d
    ports:
      - "3333:3306"

default.cnf

[mysqld]
default_authentication_plugin=mysql_native_password
character-set-server=utf8mb4
collation-server=utf8mb4_bin

[mysql]
default-character-set=utf8mb4

起動させて、、

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 8.0.16    |
+-----------+

これで用意完了。

テーブルを作成しCHECK制約を作成する

では実際にテーブルを作成し、CHECK制約を付けてみます。今回は、以下の制約を付けてみる事にしました。

• number カラム => 0 より大きく、5以下
• word カラム => アルファベット小文字のみ

構文

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

DDL文はこんな感じになりました。

CREATE TABLE tbl (
  number INT CHECK (0 < number AND number <= 5),
  word VARCHAR(20) CHECK (word REGEXP '^[a-z]+$')
);

number の CHECK制約は等号で定義し、word の CHECK制約は正規表現で定義しました。

SQLを実行してみます。

mysql> CREATE TABLE `tbl1` ( number INT CHECK (0 < number AND number <= 5), word VARCHAR(20) CHECK (word REGEXP '^[a-z]+$') );
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE `tbl1`;
CREATE TABLE `tbl1` (
 `number` int(11) DEFAULT NULL,
 `word` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
 CONSTRAINT `tbl1_chk_1` CHECK (((0 < `number`) and (`number` <= 5))),
 CONSTRAINT `tbl1_chk_2` CHECK (regexp_like(`word`,_utf8mb4'^[a-z]+$'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

CHECK制約が張れました。

動作確認

実際にINSERT文を投げて動作を確認してみます。まずは、制約にかからない値をインサートします。

mysql> INSERT INTO `tbl1` VALUES (1, 'hello');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM `tbl1`;
+--------+-------+
| number | word  |
+--------+-------+
|      1 | hello |
+--------+-------+
1 row in set (0.01 sec)

問題なく挿入できました。次に、制約に引っかかる値を挿入してみます。まずは、numberの方から

mysql> INSERT INTO `tbl1` VALUES (0, 'hello');
ERROR 3819 (HY000): Check constraint 'tbl1_chk_1' is violated.

０より大きい制約を付けているので、INSERTが弾かれている事が確認できます。続いて、wordの方も。

mysql> INSERT INTO `tbl1` VALUES (1, '12345');
ERROR 3819 (HY000): Check constraint 'tbl1_chk_2' is violated.

こちらも制約で弾かれている事が確認できました。

制約名を設ける

動作確認は出来たものの、エラーメッセージでは「tbl1_chk_1」や「tbl1_chk_2」と表示されていて何の制約に引っかかったのかよくわかりません。

制約名も付けられるので、今度はそれを設定してみます。

構文

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

ここですね。

DDL文は以下になりました。

CREATE TABLE `tbl1` (
 number INT CONSTRAINT only_5_numbers CHECK (0 < number AND number <= 5),
 word VARCHAR(20) CONSTRAINT only_lowercase_alphabet CHECK (word REGEXP '^[a-z]+$')
);

テーブルを作成します。

mysql> CREATE TABLE `tbl2` (number INT CONSTRAINT only_5_numbers CHECK (0 < number AND number <= 5), word VARCHAR(20) CONSTRAINT only_lowercase_alphabet CHECK (word REGEXP '^[a-z]+$'));
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE `tbl2`;
CREATE TABLE `tbl2` (
 `number` int(11) DEFAULT NULL,
 `word` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
 CONSTRAINT `only_5_numbers` CHECK (((0 < `number`) and (`number` <= 5))),
 CONSTRAINT `only_lowercase_alphabet` CHECK (regexp_like(`word`,_utf8mb4'^[a-z]+$'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

うん、いい感じです。ではレコードを挿入してみます。まずは制約にかからないレコードから。

mysql> INSERT INTO `tbl2` VALUES (1, 'hello');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `tbl2`;
+--------+-------+
| number | word  |
+--------+-------+
|      1 | hello |
+--------+-------+
1 row in set (0.00 sec)

問題ないです。では制約に引っかかる値をINSERTします。まずはnumberから。

mysql> INSERT INTO `tbl2` VALUES (0, 'hello');
ERROR 3819 (HY000): Check constraint 'only_5_numbers' is violated.

制約名を設定したのでどんな制約に引っかかったのかわかりやすくなりました。次にwordの方も

mysql> INSERT INTO `tbl2` VALUES (1, '12345');
ERROR 3819 (HY000): Check constraint 'only_lowercase_alphabet' is violated.

うん、いい感じです。

ちなみに以下のSQLでもCHECK制約を確認できます。

mysql> SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS;
+--------------------+-------------------+-------------------------+------------------------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME         | CHECK_CLAUSE                             |
+--------------------+-------------------+-------------------------+------------------------------------------+
| def                | sample            | only_5_numbers          | ((0 < `number`) and (`number` <= 5))     |
| def                | sample            | only_lowercase_alphabet | regexp_like(`word`,_utf8mb4\'^[a-z]+$\') |
+--------------------+-------------------+-------------------------+------------------------------------------+
2 rows in set (0.01 sec)

CHECK制約を外す

CHECK制約を外す場合には、制約名を指定してDROPします。

構文

ALTER TABLE <table_name> DROP CHECK symbol;

mysql> ALTER TABLE tbl2 DROP CHECK only_lowercase_alphabet;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS;
+--------------------+-------------------+-----------------+--------------------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE                         |
+--------------------+-------------------+-----------------+--------------------------------------+
| def                | sample            | only_5_numbers  | ((0 < `number`) and (`number` <= 5)) |
+--------------------+-------------------+-----------------+--------------------------------------+
1 row in set (0.01 sec)

CHECK制約が外れたので、これまで弾かれていた値が格納できます。

mysql> INSERT INTO `tbl2` VALUES (1, '12345');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `tbl2`;
+--------+-------+
| number | word  |
+--------+-------+
|      1 | hello |
|      1 | 12345 |
+--------+-------+
2 rows in set (0.00 sec)

あとからCHECK制約を付与する

既に存在しているテーブルのカラムに対してCHECK制約を設定してみます。

構文

ALTER TABLE <table_name> ADD [CONSTRAINT [symbol]] CHECK (condition) [[NOT] ENFORCED]

さっき外した制約を再度付与してみます。

mysql> ALTER TABLE `tbl2` ADD CONSTRAINT `only_lowercase_alphabet` CHECK (word REGEXP '^[a-z]+$');
ERROR 3819 (HY000): Check constraint 'only_lowercase_alphabet' is violated.

先程追加した word=12345 のレコードが、付与したいCHECK制約に違反しているため制約を付与できない状態なので、このレコードを削除してから再度制約を付与してみます。

mysql> DELETE FROM `tbl2` WHERE word='12345';
Query OK, 1 row affected (0.01 sec)

mysql> ALTER TABLE `tbl2` ADD CONSTRAINT `only_lowercase_alphabet` CHECK (word REGEXP '^[a-z]+$');
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS;
+--------------------+-------------------+-------------------------+------------------------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME         | CHECK_CLAUSE                             |
+--------------------+-------------------+-------------------------+------------------------------------------+
| def                | sample            | only_5_numbers          | ((0 < `number`) and (`number` <= 5))     |
| def                | sample            | only_lowercase_alphabet | regexp_like(`word`,_utf8mb4\'^[a-z]+$\') |
+--------------------+-------------------+-------------------------+------------------------------------------+
2 rows in set (0.00 sec)

今度は正常に付与できました。もちろん、再度弾かれるようになります。

mysql> INSERT INTO `tbl2` VALUES (1, '12345');
ERROR 3819 (HY000): Check constraint 'only_lowercase_alphabet' is violated.

mysql> UPDATE `tbl2` SET word='12345';
ERROR 3819 (HY000): Check constraint 'only_lowercase_alphabet' is violated.

制約の強制/非強制で切り替える

付与したCHECK制約を強制/非強制で切り替える事ができます。

構文

ALTER TABLE <table_name> ALTER CHECKシンボル[NOT] ENFORCED

現在は制約がかかっている状態なので、これを非強制にしてみます。

mysql> ALTER TABLE `tbl2` ALTER CHECK only_lowercase_alphabet NOT ENFORCED;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE `tbl2`;
CREATE TABLE `tbl2` (
 `number` int(11) DEFAULT NULL,
 `word` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
 CONSTRAINT `only_5_numbers` CHECK (((0 < `number`) and (`number` <= 5))),
 CONSTRAINT `only_lowercase_alphabet` CHECK (regexp_like(`word`,_utf8mb4'^[a-z]+$')) /*!80016 NOT ENFORCED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

非強制状態になったので、制約を受けずにインサートできます。

mysql> INSERT INTO `tbl2` VALUES (1, '12345');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM `tbl2`;
+--------+-------+
| number | word  |
+--------+-------+
|      1 | hello |
|      1 | 12345 |
+--------+-------+
2 rows in set (0.00 sec)

これでも・・どの状況で使うんだろう。ちょっとケースが思いつかなかった。

まとめ

これまではアプリケーション側でのバリデーションや操作が最後の砦でしたが、MySQL側でもこういった制約が付けられるようになったことで、よりDBに格納する値の保証ができるようになって良い感じでした。

参考
MySQL Server Blog News from the MySQL Server Team
日々の覚書：MySQL 8.0.16にCHECK制約が来て、NOT ENFORCEDなんてものまでついてきた