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

MySQLは参照整合性のための外部キー（FOREIGN KEY）をサポートしていますが、残念ながらCHECK制約はサポートしていません。しかし、トリガー（TRIGGER）を活用することで、CHECK制約と同等の動作を実現できます。以下の例でその方法を具体的に見ていきましょう。

例：車両登録番号テーブルのケース

ここでは、「car」という名前のテーブルを想定します。このテーブルには、次のような固定形式の登録番号を格納することになっています。

• 英字2文字
• ハイフン（-）1つ
• 数字3桁
• ハイフン（-）1つ
• 英字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)

この「AB-2X5-YZ」は、数字が入るべき位置に英字「X」が含まれているため、固定構文に違反した無効な値です。本来であれば挿入を拒否したいところですが、MySQLにはCHECK制約がないため、そのまま登録されてしまいます。

BEFORE INSERTトリガーでCHECK制約をエミュレートする

そこで、不正な形式のデータ挿入を防ぐために、BEFORE INSERTトリガーを作成します。トリガー内ではRLIKE演算子による正規表現チェックを行い、形式に合わない場合はSIGNALステートメントでエラーを発生させます。

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による正規表現検証： 「^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$」というパターンで、値が「英字2文字-数字3桁-英字2文字」の形式に完全一致するかを判定します。
• SIGNAL SQLSTATE '45000'： 形式が不正な場合にカスタムエラーを発生させ、INSERT処理を中断します。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 INSERTトリガーとRLIKE、SIGNALを組み合わせることで、MySQLでもCHECK制約と同様のデータ整合性チェックを実現できます。UPDATE時にも同様の検証が必要な場合は、BEFORE UPDATEトリガーを同じ要領で追加するとよいでしょう。