ビューを使ってMySQLのCHECK制約をエミュレートする方法

MySQLは参照整合性を保証するための外部キー（FOREIGN KEY）はサポートしていますが、残念ながらCHECK制約はサポートしていません。しかし、WITH CHECK OPTIONを付けたビュー（またはトリガー）を活用することで、CHECK制約と同等のデータ検証を実現できます。ここでは、ビューを使用したエミュレート方法を具体例とともに解説します。

前提となるテーブルと課題

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

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)

'AB-2X5-YZ' は、本来数字が入るべき箇所に英字が含まれているため、固定形式に反する無効な値です。しかしMySQLにはCHECK制約が存在しないため、テーブルへの直接INSERTではこのような不正データがそのまま格納されてしまいます。

WITH CHECK OPTION付きビューでCHECK制約を再現する

そこで、正規表現（RLIKE）によるパターンマッチングで有効な行だけを返すビューを、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)

このビューに対して無効な値を挿入または更新しようとすると、CHECK OPTIONによってエラーが発生します。

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)

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

ポイントと注意点

• ビューのWHERE句（RLIKEによる正規表現）が、事実上のCHECK制約として機能します。
• INSERTもUPDATEも、ビュー経由で実行された場合のみ検証が働きます。ベーステーブル（car1）へ直接書き込むと制約は適用されません。
• 実際に運用する場合は、アプリケーション側の書き込み先をこのビューに変更するか、権限設定でベーステーブルへの直接INSERT/UPDATEを禁止するなどの対策が必要です。

このように、WITH CHECK OPTION付きのビューを経由してデータ操作を行うことで、MySQLでもCHECK制約と同様のデータ妥当性検査を実現できます。