MySQLでGENERATED COLUMNを使ってCHECK制約をエミュレートする方法

MySQLは参照整合性を確保するための外部キー（FOREIGN KEY）をサポートしていますが、CHECK制約はサポートしていません。ただし、トリガーやGENERATED COLUMN（生成カラム）を活用することで、CHECK制約と同等の動作を実現できます。本記事では、GENERATED COLUMNを使った方法を具体例とともに解説します。

例：登録番号の形式を検証する

「car」というテーブルがあり、登録番号は「英字2文字-数字3桁-英字2文字」という固定形式に従うものとします。

mysql> Create table car2 (number char(9));
Query OK, 0 rows affected (0.32 sec)

mysql> Insert into car2 values('AB-235-YZ');
Query OK, 1 row affected (0.10 sec)

上記の値は有効な形式です。しかし、次のクエリで挿入する値はどうでしょうか。

mysql> insert into car2 values('AB-2X5-YZ');
Query OK, 1 row affected (0.04 sec)

この値は無効です。数字の間に英字が含まれており、定めた固定形式に反しているためです。しかし、このままでは誤ったデータもそのまま登録されてしまいます。

GENERATED COLUMNでINSERT・UPDATE時の値を検証する

そこで、VIRTUAL GENERATEDカラムをNOT NULL制約付きで追加し、正規表現による検証を組み込みます。

mysql> Alter table car2 add column number_validate char(0) as (case when number
rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
    -> then '' end)
    -> virtual not null;
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> Describe car2;
+-----------------+---------+------+-----+---------+-------------------+
| Field           | Type    | Null | Key | Default | Extra             |
+-----------------+---------+------+-----+---------+-------------------+
| number          | char(9) | YES  |     | NULL    |                   |
| number_validate | char(0) | NO   |     | NULL    | VIRTUAL GENERATED |
+-----------------+---------+------+-----+---------+-------------------+
2 rows in set (0.00 sec)

mysql> Insert into car2 (number) values('AB-235-YZ');
Query OK, 1 row affected (0.09 sec)

上記のクエリは有効な番号を挿入しているため、正常に受け入れられます。一方、以下のクエリでは無効な番号の挿入・更新がエラーとなり、拒否されます。

mysql> Insert into car2 (number) values('AB-2X5-YZ');
ERROR 1048 (23000): Column 'number_validate' cannot be null

mysql> Update car2 set number='AB-2X5-YZ';
ERROR 1048 (23000): Column 'number_validate' cannot be null

仕組みのポイント

このテクニックの鍵は、VIRTUAL GENERATEDカラム「number_validate」をNOT NULL制約付きで定義している点にあります。値が正規表現パターンにマッチした場合のみ空文字列が返され、マッチしない場合はNULLが返されるため、NOT NULL制約違反としてエラーが発生します。これにより、INSERTとUPDATEの両方の操作に対して、CHECK制約と同等のデータ検証を実現できるのです。