MySQL 5.7で生成カラムを使ってCHECK制約もどきを実装する

MySQL 5.7で生成カラムを使って関数INDEXを作成する の続きです。

MySQLでは、DDLにCHECK制約（検査制約）を記述することはできますが、動作上は無視されてしまいます。
そのため、MySQL 5.6までであれば、トリガで代用するのが定番でした。

MySQL 5.7で実装された生成カラム機能を使うと、CHECK制約と同じようなことが可能になります。

2019/12/01追記：
MySQL 8.0.16から、CHECK制約が有効に機能するようになりました。

[参考情報]

• 詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド P.236～
• Yakst　CHECK制約をエミュレートする新旧の方法とドメイン (MySQL Server Blogより)

実装例

定義されたドメイン（＝登録値として許される範囲）から外れる値が登録されないようにするケースを考えます。

先のエントリで使ったテーブル定義では、都道府県（pref）カラムの値から地方／地域（area）カラムの値を導出する形で生成カラムを使っていましたが、この生成カラムは、

「都道府県名として正しくない値が（で）挿入／更新されるのを防ぐ」

ことに流用することも可能です。

先のエントリで示したテーブル定義を、以下のように変更します。

制約を掛けるためのテーブル定義

mysql> CREATE TABLE `virt_test`.`yuruchara3` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(100) NOT NULL,
    ->   `pref` varchar(3) NOT NULL,
    ->   `area` varchar(3) AS ((case when (`pref` = '北海道') then '北海道'
    ->                               when (`pref` in ('青森','岩手','秋田','宮城','山形','福島')) then '東北'
    ->                               when (`pref` in ('茨城','栃木','群馬','埼玉','千葉','東京','神奈川')) then '関東'
    ->                               when (`pref` in ('山梨','長野','新潟','富山','石川','福井','静岡','愛知','岐阜')) then '中部'
    ->                               when (`pref` in ('三重','滋賀','京都','大阪','兵庫','奈良','和歌 山')) then '近畿'
    ->                               when (`pref` in ('鳥取','島根','岡山','広島','山口')) then '中国'
    ->                               when (`pref` in ('香川','愛媛','徳島','高知')) then '四国'
    ->                               when (`pref` in ('福岡','佐賀','長崎','熊本','大分','宮崎','鹿児島','沖縄')) then '九州' else NULL end)) NOT NULL,
    ->   `point` int(10) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_pref` (`pref`),
    ->   KEY `idx_area` (`area`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

yuruchara2テーブルとの違いが分かりづらいですが、

• areaカラムにNOT NULL制約を付けたまま、
• 入力値が、列挙された都道府県名に合致しない場合の、else時に返す値をNULLにする（変更前は「その他」）

ことで、

• 正しくない都道府県名がやってくると、
• areaカラムでNOT NULL制約違反になる

ようにしてあります。

例えば、都道府県名と間違えて市町村名（「須崎」）を入れようとした場合、

以前のテーブルでINSERT

mysql> INSERT INTO virt_test.yuruchara2 SET name = 'しんじょう君', pref = '須崎', point = 4345960;
Query OK, 1 row affected (0.00 sec)

普通にINSERTされてしまいますが、変更後のテーブル定義では、

変更後のテーブルでINSERT

mysql> INSERT INTO virt_test.yuruchara3 SET name = 'しんじょう君', pref = '須崎', point = 4345960;
ERROR 1048 (23000): Column 'area' cannot be null

怒られます。

出力されるエラーメッセージが分かりづらいのが難点ですが、CHECK制約のかわりに使えることがイメージできたと思います。

但し、生成カラムの式にサブクエリを含めることはできません。

例のように、値ドメインを都道府県名に限定するような場合はいいとして（都道府県名は数が少なく、変化することもほどんどないため）、市町村名をチェックするような用途には不適当です。

他テーブルを参照するような制約を掛けるときは、従来通りトリガなどを利用することになります。

---

【おまけ】
MySQL 5.7関連投稿記事へのリンクを集めました。

• Qiitaに投稿したMySQL 5.7関連記事