免責事項
この記事はGuilhem Bichot氏によるMySQL Server Blogの投稿「New and old ways to emulate CHECK constraints, DOMAIN」(2016/2/2)をユーザが翻訳したものであり、Oracle公式の文書ではありません。
データの正しさを保つには様々な方法があります。1つは参照整合性で、外部キーとして知られているものです。他の方法として、CHECK制約があります。MySQLでは外部キーをサポートしていますが、CHECK制約をサポートしていません。本日は、これをエミュレートする3つの方法についてみていきたいと思います。
- トリガー
- ビュー
- 生成列 (MySQL 5.7新機能)
これは他のSQLの機能(ドメイン: DOMAIN)とも関連があります。簡単にいうと、ドメインとはユーザー定義型で、元となる型(INT、CHAR、...)、デフォルト値、許可される値の制約、すなわちある種の簡単なCHECK制約、から構成されるものです。CHECK制約をエミュレートできれば、ドメインの一部の機能がある、ということができます。
ここでフランスでの車の登録番号をカラムとして持つ車のテーブルについて考えてみましょう。 この番号は形式が定義されています。すなわち2文字、ダッシュ、数字3文字、ダッシュ、2文字です。 もちろん、この例はUUID、電話番号、IPアドレス、郵便番号など、形式が定義されたものを保存するときはいつでもあてはまるものです。
create table cars (number char(9));
insert into cars values('AS-229-ZT'); # 正しい
我々はMySQLで次のように不正なデータを登録できないようにしたいのです。
mysql> insert into cars values('AS-2X9-ZT'); # "X"が数字が入るべきところにある
Query OK, 1 row affected (0,01 sec) # これは許可されるべきではない
トリガーを利用する
delimiter $
create trigger cars_number_validate before insert on cars
for each row
begin
if new.number not rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
then
signal sqlstate '45000' set message_text = 'bad number';
end if;
end$
delimiter ;
挿入しようとしている番号が規定の正規表現にマッチしない場合、シグナルがエラーを送ります。 次の結果になります。
mysql> delete from cars;
mysql> insert into cars values('AS-2X9-ZT'); # "X"が数字が入るべきところにある
ERROR 1644 (45000): bad number
mysql> insert into cars values('AS-229-ZT'); # 正しい
Query OK, 1 row affected (0,01 sec)
すばらしい。しかしながら、これは不正な値の挿入を防ぐ一方で、正しい値が不正な値に更新されることは防ぎません。
mysql> update cars set number='AS-2X9-ZT';
Query OK, 1 rows affected (0,00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
これを修正するためには追加のBEFORE UPDATEに対してのトリガーが必要となります。
delimiter $
create trigger cars_number_validate_2 before update on cars
for each row
begin
if new.number not rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
then
signal sqlstate '45000' set message_text = 'bad number';
end if;
end$
delimiter ;
これでUPDATEが意図したとおり失敗するようになります。
mysql> update cars set number='AS-2X9-ZT';
ERROR 1644 (45000): bad number
トリガーベースの方法は確実にうまく動きます。トリガーではもっと複雑なチェックもできます(番号が他のテーブルの何らかの値と関連があるか、などをサブクエリを利用することによってチェックするなど)。しかし、トリガーを作成するには次のことが必要となります。
- 10行のプロシージャーのコードを2回書く
TRIGGER権限
ビューをCHECKオプションと共に利用する
トリガーをドロップして、テーブルを空にして違う方法をみてみましょう。carsテーブルにcars_checkedビューを作成します。このビューには正しい行のみを見せるWHERE句があります。このビューにはWITH CHECK OPTIONが設定されており、これによって挿入及び更新がビューに対して行われた際に、新しい行がビューに現れないもの、すなわち不正となる行が拒否されます。
mysql> drop trigger cars_number_validate_2;
mysql> drop trigger cars_number_validate;
mysql> delete from cars;
mysql> create view cars_checked as
select * from cars where
number rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
with check option;
mysql> insert into cars_checked values('AS-2X9-ZT'); # "X"が数字が入るべきところにある
ERROR 1369 (HY000): CHECK OPTION failed 'test.cars_checked'
mysql> insert into cars_checked values('AS-229-ZT'); # 正しい
Query OK, 1 row affected (0,00 sec)
mysql> update cars_checked set number='AS-2X9-ZT';
ERROR 1369 (HY000): CHECK OPTION failed 'test.cars_checked'
実際に利用する際は、ユーザーのcarsテーブルの書き込み権限を剥奪し、チェックをすりぬけられないようにし、cars_checkedビューのみに書き込み権限を付与します。
トリガーと同様に、この方法も完全に動作し、融通が利きます(ビューのWHERE句でサブクエリが使えます)。しかし、下記が必要となります。
CREATE VIEW権限- ユーザーから権限を剥奪し、他の権限を付与する
- アプリケーションを修正する必要があるかもしれない(carsの代わりにcars_checkedを利用)
生成列を利用する
MySQL 5.7から利用できる新しい方法を試してみましょう。
mysql> alter table cars add column
number_validate char(0) as
(case when number rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
then '' end)
virtual not null;
mysql> insert into cars values('AS-229-ZT'); # 正しい
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into cars (number) values('AS-229-ZT'); # 正しい
Query OK, 1 row affected (0,00 sec)
mysql> insert into cars (number) values('AS-2X9-ZT'); # "X"が数字が入るべきところにある
ERROR 1048 (23000): Column 'number_validate' cannot be null
mysql> update cars set number='AS-2X9-ZT';
ERROR 1048 (23000): Column 'number_validate' cannot be null
number_validate生成列を追加しました。CHAR(0) NOT NULLとし、空文字列でレコードがディスク容量を消費しないようにしています。数値が正しい形式ならば、新しく作成したカラムが空文字列("")と評価され新しいカラムの型に合致するため挿入や更新が成功します。数値が正しい形式でなければ、新しく作成したカラムはNULLと評価され(暗黙の"ELSE NULL"がCASE句にあります)、NOT NULL制約によって拒否されます。
ところで、元のinsert into cars values(‘AS-229-ZT’)が失敗するようになっています。2つのカラムを持つようになったため、どのカラムを挿入しているのかを指定するか(上記のinsert into cars (number)のように)、生成列にdefaultを利用するかする必要があります。
mysql> insert into cars values('AS-229-ZT', default); # 正しい
Query OK, 1 row affected (0,00 sec)
CHECK制約をエミュレートする3つの方法を比べてみてください。
- トリガーやビューは強力ですが、一方で設定適用が複雑です。
- 生成列の方法は、機能的に劣ります。(生成列はサブクエリーを含むことができない、など)。しかし設定適用が簡単です。テーブルに直接設定が付与でき、1行のSQL文で良いためです。この例のような簡単なバリデーションチェックであれば、私の意見としては、こちらに分があると思います。
ここで紹介した方法が、MySQLのテーブルにデータのバリデーションを実装する上で役に立つことを願います。MySQLを使ってくれてありがとう！
