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

MySQLは参照整合性を保証するための外部キー（FOREIGN KEY）をサポートしていますが、CHECK制約はサポートしていません。しかし、トリガーを活用することで、CHECK制約と同等の機能を実現できます。以下の具体例でその方法を解説します。

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

「car」というテーブルがあり、登録番号が「英字2文字＋ハイフン＋数字3文字＋ハイフン＋英字2文字」という固定形式である必要があるとします。まず、次のようにテーブルを作成します。

mysql> CREATE TABLE car (number CHAR(9));
Query OK, 0 rows affected (0.32 sec)

mysql> INSERT INTO car VALUES('AB-235-YZ');
Query OK, 1 row affected (0.10 sec)

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

mysql> INSERT INTO car VALUES('AB-2X5-YZ');
Query OK, 1 row affected (0.04 sec)

この値は数字の中に文字が混在しているため、定めた形式に違反しています。しかしCHECK制約がないため、そのまま挿入されてしまいます。

BEFORE INSERTトリガーによるCHECK制約のエミュレート

不正な値の挿入を防ぐため、次のようにBEFORE INSERTトリガーを作成します。

mysql> DELIMITER //
mysql> CREATE TRIGGER car_insert_value BEFORE INSERT ON car
    -> FOR EACH ROW
    -> BEGIN
    -> IF NEW.number NOT RLIKE '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
    -> THEN
    -> SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Not a valid Number';
    -> END IF;
    -> END //
Query OK, 0 rows affected (0.15 sec)

mysql> DELIMITER ;
mysql> DELETE FROM car;
Query OK, 2 rows affected (0.06 sec)

このトリガーでは、RLIKE演算子と正規表現を使って値の形式を検証しています。形式に合わない場合は、SIGNALステートメントによってSQLSTATE '45000'（ユーザー定義エラー）を発生させます。

この状態で誤った番号を挿入しようとすると、トリガーが動作してエラーが発生し、挿入が阻止されます。

mysql> INSERT INTO car VALUES('AB-2X5-YZ');
ERROR 1644 (45000): Not a Valid Number

一方、正しい形式の値であれば、問題なく挿入できます。

mysql> INSERT INTO car VALUES('AB-235-YZ');
Query OK, 1 row affected (0.04 sec)

BEFORE UPDATEトリガーによるCHECK制約のエミュレート

更新時にも同様の問題が起こります。トリガーを作成する前は、誤った値へのUPDATEも阻止されません。

mysql> UPDATE car SET number='AB-2X5-YZ';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

上記のクエリでは、テーブル「car」に誤った値がそのまま更新されてしまいました。そこで、不正なデータへの更新を防ぐため、次のようにBEFORE UPDATEトリガーを作成します。

mysql> DELIMITER //
mysql> CREATE TRIGGER car_update_value BEFORE UPDATE ON car
    -> FOR EACH ROW
    -> BEGIN
    -> IF NEW.number NOT RLIKE '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
    -> THEN
    -> SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Not a valid number';
    -> END IF;
    -> END //
Query OK, 0 rows affected (0.11 sec)

mysql> DELIMITER ;

mysql> UPDATE car SET number='AB-2X5-YZ';
ERROR 1644 (45000): Not a valid number

これにより、INSERT時・UPDATE時のどちらでも正規表現による形式チェックが実行され、CHECK制約と同様のデータ整合性を維持できるようになります。MySQLでCHECK制約が使えない環境でも、トリガーを組み合わせることで堅牢なバリデーションを実装できます。