MySQL 8.0.16 で実装された CHECK 制約を（いまさら）試してみる

MySQL 8.0.16 で（ようやく）実装された CHECK 制約について、いまさらですが試してみます。

※配布開始 1 周年を迎えた**「MySQL 8.0 の薄い本」**を、8.0.20 対応版改訂を機にリニューアルし、実行例を増やすための記事です。はい。

• 「MySQL 8.0 の薄い本」を作って配っている話

CHECK 制約とは

簡単に言うと**「記述した条件式に合わない行の挿入・更新を防ぐ」**ものです。

• INSERT, UPDATE, REPLACE, LOAD DATA, LOAD XMLの実行時に評価される
• 評価の結果がFALSEの場合にエラーになる（行の挿入・更新が行われない）

なお MySQL 8.0 の場合、以下のようなものは条件式に記述できません。

• CONNECTION_ID(), CURRENT_USER(), NOW()のような非決定性関数
• ストアドファンクション・ストアドプロシージャ・ユーザ定義関数（UDF）
• 変数
• サブクエリ など

実行例

単純な例（単一列の値の範囲を制限）

整数値を持つ列c1の値を、1 以上 10 以下の範囲に制限してみます。

単純な例

mysql> CREATE DATABASE check_test;
Query OK, 1 row affected (0.00 sec)

mysql> USE check_test;
Database changed
mysql> CREATE TABLE t1
    -> (
    ->   id INT PRIMARY KEY AUTO_INCREMENT,
    ->   c1 INT CHECK (0 < c1 AND c1 <= 10)
    -> );
Query OK, 0 rows affected (0.03 sec)
# 他の制約と同様、列定義の後ろに書ける。

mysql> INSERT INTO t1 SET c1 = 1;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 SET c1 = 10;
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 SET c1 = 0;
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.
# 範囲外なのでエラー
mysql> INSERT INTO t1 SET c1 = 11;
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.
# 範囲外なのでエラー

複数列に対する制約の例

次の例では、複数列を対象に制約条件を少し複雑にしてみます。

• 都道府県（prefecture）はNOT NULL（必須／CHECK 制約ではなくNOT NULL制約で）
• 東京都の場合は以下のいずれか
  • 市町村（city_town_village）が空欄（NULL）かつ区（ward）が必須（NOT NULL）
  • 市町村（city_town_village）が必須（NOT NULL）かつ区（ward）が空欄（NULL）
• 東京都以外の場合は市町村が必須（NOT NULL）

複数列に対する制約の例

mysql> CREATE TABLE t2
    -> (
    ->   id INT PRIMARY KEY AUTO_INCREMENT,
    ->   prefecture VARCHAR(4) NOT NULL,
    ->   city_town_village VARCHAR(10),
    ->   ward VARCHAR(10),
    ->   CONSTRAINT ctv_ward_blank CHECK
    ->   (
    ->     (prefecture = '東京都' AND city_town_village IS NULL AND ward IS NOT NULL) OR
    ->     (prefecture = '東京都' AND city_town_village IS NOT NULL AND ward IS NULL) OR
    ->     (prefecture <> '東京都' AND city_town_village IS NOT NULL)
    ->   )
    -> );
Query OK, 0 rows affected (0.02 sec)
# 単一列の制約の場合もこのように列定義から独立して書くことができる。

mysql> INSERT INTO t2 SET prefecture = '東京都', ward = '千代田区';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 SET prefecture = '東京都', city_town_village = '八王子市';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t2 SET prefecture = '愛知県', city_town_village = '豊田市';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t2 SET prefecture = '愛知県', city_town_village = '名古屋市', ward = '中区';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 SET prefecture = '東京都', city_town_village = '町田市', ward = '多摩区';
ERROR 3819 (HY000): Check constraint 'ctv_ward_blank' is violated.
# エラーの原因は「町田は神奈川」ではなく、東京都なのに市町村と区が同時に指定されたから。
mysql> INSERT INTO t2 SET prefecture = '東京都';
ERROR 3819 (HY000): Check constraint 'ctv_ward_blank' is violated.
# 東京都では市町村または区のいずれかが指定されていないとエラーになる。
mysql> INSERT INTO t2 SET prefecture = '愛知県';
ERROR 3819 (HY000): Check constraint 'ctv_ward_blank' is violated.
# 東京都以外の場合は市町村が指定されていないとエラーになる。
mysql> SELECT * FROM t2 ORDER BY id;
+----+------------+-------------------+--------------+
| id | prefecture | city_town_village | ward         |
+----+------------+-------------------+--------------+
|  1 | 東京都     | NULL              | 千代田区     |
|  2 | 東京都     | 八王子市          | NULL         |
|  3 | 愛知県     | 豊田市            | NULL         |
|  4 | 愛知県     | 名古屋市          | 中区         |
+----+------------+-------------------+--------------+
4 rows in set (0.00 sec)

---

• Qiitaに投稿したMySQL 8.0関連記事
• MySQL 8.0 の薄い本（無料で配布中！）