BEFORE UPDATEトリガーを使ってCHECK制約をエミュレートし、テーブルの値の更新を検証する方法

MySQLは参照整合性を実現するための外部キー（FOREIGN KEY）をサポートしていますが、残念ながらCHECK制約はサポートしていません。しかし、トリガーを活用すれば、CHECK制約と同等の動作をエミュレートすることが可能です。ここでは、BEFORE UPDATEトリガーを使って、UPDATE時に入力値を検証する方法を具体例とともに解説します。

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

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

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

トリガーなしでは不正な値も更新できてしまう

この状態で、誤った形式の値を更新しようとしても、MySQLは何も警告せずにそのまま受け入れてしまいます。

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

上記のクエリでは、形式に反する不正な値「AB-2X5-YZ」がそのままテーブルに書き込まれてしまいました。これを防ぐために、BEFORE UPDATEトリガーを作成します。

BEFORE UPDATEトリガーを作成してCHECK制約をエミュレートする

次のクエリでは、新しい値が正規表現パターンに一致するかどうかをRLIKEで判定し、一致しない場合はSIGNAL文によってエラーを発生させて更新を中断させます。

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

動作のポイント

• RLIKE：正規表現によるパターンマッチングを行い、値が「英字2文字-数字3文字-英字2文字」の形式に一致するかを検証します。
• SIGNAL SQLSTATE '45000'：条件を満たさない場合にカスタムエラーを発生させ、UPDATE処理を強制的に中断します。SQLSTATE「45000」はユーザー定義のエラーを示す汎用的なステートです。

このようにトリガーを設定した後は、正規表現パターンに一致しない値で更新しようとすると「ERROR 1644 (45000): Not a valid number」というエラーが返され、不正なデータがテーブルに書き込まれるのを防げます。同様の手法はINSERT時にも応用でき、BEFORE INSERTトリガーを使えば新規行の挿入に対しても同じ検証を行うことができます。