MySQLでVIEWを使用してCHECK制約をエミュレートする方法

MySQLは参照整合性を保つための外部キー（FOREIGN KEY）には対応していますが、残念ながらCHECK制約はネイティブではサポートしていません。しかし、ビュー（VIEW）とWITH CHECK OPTIONを組み合わせることで、CHECK制約と同様の動作をエミュレートすることが可能です。この記事では、具体的な例を通じてその方法を解説します。

具体例：登録番号の形式チェック

まず、固定の書式を持つナンバープレートの登録番号を格納するテーブル「car1」を作成してみましょう。ここでは、「英字2文字 - 数字3文字 - 英字2文字」という形式（例：AB-235-YZ）を正しい構文として扱います。

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

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

上記の「AB-235-YZ」は正しい形式の値です。しかし、次のように誤った形式のデータを挿入しようとした場合はどうなるでしょうか。

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

この値は、本来数字が入るべき位置に文字「X」が含まれているため、定めた書式に違反している無効なデータです。テーブルに直接挿入すると検証なしで登録されてしまうため、何らかの仕組みで防ぐ必要があります。

VIEWとWITH CHECK OPTIONによるエミュレート

そこで、正規表現による条件を備えたビューを作成し、WITH CHECK OPTIONを付与します。こうすることで、INSERTやUPDATEの際に条件を満たさないデータの操作が自動的に拒否されるようになります。

mysql> Create view car_invalid_check as
    -> Select * from car1 WHERE number rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
    -> with check option;
Query OK, 0 rows affected (0.12 sec)

このビューでは、RLIKE演算子と正規表現パターン ^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$ を使用して、登録番号が「英字2文字 - 数字3文字 - 英字2文字」という形式に一致する行のみを対象としています。

無効なデータの挿入・更新を試みた場合

それでは、このビュー経由で無効なデータを挿入してみましょう。

mysql> Insert into car_invalid_check values('AB-2X5-YZ');
ERROR 1369 (HY000): CHECK OPTION failed 'query.car_invalid_check'

形式に違反するデータはエラーとなり、挿入が拒否されました。一方、有効なデータであれば問題なく処理されます。

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

UPDATEの場合も同様です。既存の行を無効な形式に変更しようとすると、エラーが発生して更新が阻止されます。

mysql> Update car_invalid_check SET NUMBER = 'AB-2X5-ZT';
ERROR 1369 (HY000): CHECK OPTION failed 'query.car_invalid_check'

まとめ

このように、MySQLでCHECK制約が使えない環境でも、条件付きのビュー＋WITH CHECK OPTIONを活用すれば、INSERTやUPDATE時にデータの妥当性を検証し、不正なデータの混入を防ぐことができます。より複雑なバリデーションが必要な場合には、トリガー（TRIGGER）を併用する方法もありますので、要件に応じて使い分けると良いでしょう。