RDBMSを使うと、UNIQUE制約やCHECK制約、排他制約などを利用して、指定した条件に沿う形で、データの整合性を保つことができる。しかし、「同じ電話番号を複数の顧客が使ってよいのは、同じ家族に所属している場合のみ」というような、制約の前提に他テーブルを使う(テーブルを跨ぐ)ケースは、よくでてくるわりに実現しにくい。この記事では、そのような制約をアプリケーションではなくPostgreSQLによって実現する方法を紹介する。
TL;DR
テーブル跨ぎの整合性を保つための手段として、制約トリガー(CONSTRAINT TRIGGER)がある。手順の例は以下の通り。
- 不整合を検出するビューを用意する
- ビューに基づいて制約違反を判定する関数を定義する
- 制約トリガーを定義し、更新・挿入・削除時に整合性を検証する
前提(ユースケースの概要)
今回は以下のような顧客と家族の情報を例にする。
- 顧客は電話番号を持っている(こともある)
- 顧客は家族に所属している(こともある)
- 電話番号は原則として別の顧客と重複してはならない
- 同じ家族に所属する場合のみ重複することができる
- 電話番号も家族もあとから変更される
手順
1. 顧客・家族テーブルとインデックスの定義(準備)
CREATE TABLE customer ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE customer_phone ( customer_id UUID PRIMARY KEY REFERENCES customer(id), phone_number TEXT ); CREATE INDEX customer_phone_phone_number_index ON customer_phone (phone_number); CREATE TABLE family ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY ); CREATE TABLE family_member ( family_id UUID NOT NULL REFERENCES family(id), customer_id UUID NOT NULL REFERENCES customer(id), PRIMARY KEY (family_id, customer_id) ); CREATE INDEX family_member_family_id_index ON family_member (family_id); CREATE INDEX family_member_customer_id_index ON family_member (customer_id);
2. 不整合検出用ビューの作成
不整合となる行を抽出できるビューを準備する。ここでは、同じ電話番号を持ちながら、異なる family_id に所属している顧客ペアを抽出する。
CREATE OR REPLACE VIEW customer_phone_number_conflicts AS WITH base AS ( SELECT c.id AS customer_id, cp.phone_number, fm.family_id FROM customer c LEFT JOIN customer_phone cp ON c.id = cp.customer_id LEFT JOIN family_member fm ON c.id = fm.customer_id ), pairs AS ( SELECT t1.customer_id AS t1_customer_id, t2.customer_id AS t2_customer_id, t1.phone_number AS t1_phone_number, t2.phone_number AS t2_phone_number, t1.family_id AS t1_family_id, t2.family_id AS t2_family_id FROM base t1 JOIN base t2 ON t1.customer_id <> t2.customer_id ) SELECT * FROM pairs WHERE t1_phone_number IS NOT NULL AND t1_phone_number = t2_phone_number AND ( t1_family_id IS NULL OR t2_family_id IS NULL OR t1_family_id <> t2_family_id );
CTEは使わなくても良いが、重複検知のような場合には、以下の3ステップにするとわかりやすい。
- 不整合検知に必要な情報を集める
- 1.の行の組み合わせを生成する
- 2.から不整合になっている行を探す
ビューの作成自体も必須ではないが、ビューを作成すると以下のようなメリットがある。
- 整合・不整合の状態を宣言的に記述できる
- トリガーは複数になるが、1つのビューを作ることで整合状態の知識を分散させずに済む(条件を変えやすい)
- 実際、この記事を書いている間にcustomerとcustomer_phoneを分割したのだけど、かなり変えやすかった
- 不整合なデータの確認が容易
- 検証に使われるクエリのチューニング(実行計画の確認など)がしやすい
3. ビューを使って整合性をチェックする関数の定義
作成したビューを参照し、行が現れたらRAISE EXCEPTIONする関数を作る。ビューのSELECT以外はほぼしないことがポイント。顧客の更新時(電話番号変更時)
CREATE OR REPLACE FUNCTION check_customer_phone_number_conflict_on_update() RETURNS TRIGGER AS $$ BEGIN IF EXISTS ( SELECT 1 FROM customer_phone_number_conflicts WHERE t1_customer_id = NEW.customer_id ) THEN RAISE EXCEPTION 'Phone number conflict: cannot update customer phone'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;
家族構成の変更時
CREATE OR REPLACE FUNCTION check_customer_phone_number_conflict_on_family_change() RETURNS TRIGGER AS $$ DECLARE target_id UUID; BEGIN IF TG_OP = 'DELETE' THEN target_id := OLD.customer_id; ELSE target_id := NEW.customer_id; END IF; IF EXISTS ( SELECT 1 FROM customer_phone_number_conflicts WHERE t1_customer_id = target_id ) THEN RAISE EXCEPTION 'Phone number conflict: cannot change family'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;
4. 関数を制約トリガーとして登録
関数を制約トリガー(CONSTRAINT TRIGGER)として登録する。 このとき、DEFERRABLE INITIALLY DEFERREDにすると、トランザクション終了時に検証できる。
なお、顧客側、家族側どちらの変更も監視しなければいけないことに注意
-- 顧客の電話番号変更時 CREATE CONSTRAINT TRIGGER check_customer_phone_number_conflict_trigger AFTER INSERT OR UPDATE OF phone_number ON customer_phone DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION check_customer_phone_number_conflict_on_update(); -- 家族構成の変更時 CREATE CONSTRAINT TRIGGER check_customer_phone_number_conflict_on_family_trigger AFTER INSERT OR UPDATE OR DELETE ON family_member DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION check_customer_phone_number_conflict_on_family_change();
検出例
パターン1:同じ家族内で同じ番号(OK)
INSERT INTO family (id) VALUES ('00000000-0000-0000-0000-000000000001'); INSERT INTO customer (id, name) VALUES ('00000000-0000-0000-0000-000000000011', 'Alice'), ('00000000-0000-0000-0000-000000000012', 'Bob'); INSERT INTO family_member (family_id, customer_id) VALUES ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000011'), ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000012'); INSERT INTO customer_phone (customer_id, phone_number) VALUES ('00000000-0000-0000-0000-000000000011', '000-0000-0000'), ('00000000-0000-0000-0000-000000000012', '000-0000-0000');
パターン2:異なる家族で同じ番号(NG)
INSERT INTO family (id) VALUES ('00000000-0000-0000-0000-000000000002'); INSERT INTO customer (id, name) VALUES ('00000000-0000-0000-0000-000000000013', 'Charlie'); INSERT INTO family_member (family_id, customer_id) VALUES ('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000013'); INSERT INTO customer_phone (customer_id, phone_number) VALUES ('00000000-0000-0000-0000-000000000013', '000-0000-0000');
INSERT 0 1 INSERT 0 1 INSERT 0 1 ERROR: Phone number conflict: cannot update customer phone CONTEXT: PL/pgSQL function check_customer_phone_number_conflict_on_update() line 7 at RAISE
パターン3:未所属同士の重複(NG)
INSERT INTO customer (id, name) VALUES ('00000000-0000-0000-0000-000000000014', 'Dana'), ('00000000-0000-0000-0000-000000000015', 'Eve'); INSERT INTO customer_phone (customer_id, phone_number) VALUES ('00000000-0000-0000-0000-000000000014', '000-1111-1111'), ('00000000-0000-0000-0000-000000000015', '000-1111-1111');
INSERT 0 2 ERROR: Phone number conflict: cannot update customer phone CONTEXT: PL/pgSQL function check_customer_phone_number_conflict_on_update() line 7 at RAISE
パターン4:電話番号が NULL の場合(OK)
INSERT INTO customer (id, name) VALUES ('00000000-0000-0000-0000-000000000016', 'Frank'), ('00000000-0000-0000-0000-000000000017', 'Grace'); INSERT INTO customer_phone (customer_id, phone_number) VALUES ('00000000-0000-0000-0000-000000000016', NULL), ('00000000-0000-0000-0000-000000000017', NULL);
パターン5:family_member を DELETE → 残った側が未所属になって重複(NG)
INSERT INTO family (id) VALUES ('00000000-0000-0000-0000-000000000005'); INSERT INTO customer (id, name) VALUES ('00000000-0000-0000-0000-000000000051', 'Alice'), ('00000000-0000-0000-0000-000000000052', 'Bob'); INSERT INTO family_member (family_id, customer_id) VALUES ('00000000-0000-0000-0000-000000000005', '00000000-0000-0000-0000-000000000051'), ('00000000-0000-0000-0000-000000000005', '00000000-0000-0000-0000-000000000052'); INSERT INTO customer_phone (customer_id, phone_number) VALUES ('00000000-0000-0000-0000-000000000051', '000-0000-0000'), ('00000000-0000-0000-0000-000000000052', '000-0000-0000'); -- Bob を家族から外す → NG(未所属で重複状態になる) DELETE FROM family_member WHERE customer_id = '00000000-0000-0000-0000-000000000052';
INSERT 0 1 INSERT 0 2 INSERT 0 2 INSERT 0 2 ERROR: Phone number conflict: cannot change family CONTEXT: PL/pgSQL function check_customer_phone_number_conflict_on_family_change() line 15 at RAISE
パターン6:phone_number の UPDATE による重複(NG)
INSERT INTO family (id) VALUES ('00000000-0000-0000-0000-000000000003'); INSERT INTO customer (id, name) VALUES ('00000000-0000-0000-0000-000000000018', 'Hank'); INSERT INTO customer_phone (customer_id, phone_number) VALUES ('00000000-0000-0000-0000-000000000018', '000-9999-9999'); INSERT INTO family_member (family_id, customer_id) VALUES ('00000000-0000-0000-0000-000000000003', '00000000-0000-0000-0000-000000000018'); UPDATE customer_phone SET phone_number = '000-0000-0000' WHERE customer_id = '00000000-0000-0000-0000-000000000018';
INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 ERROR: Phone number conflict: cannot update customer phone CONTEXT: PL/pgSQL function check_customer_phone_number_conflict_on_update() line 7 at RAISE
パターン7:トランザクション内で家族と番号を同時変更(OK)
BEGIN; INSERT INTO customer (id, name) VALUES ('00000000-0000-0000-0000-000000000019', 'Ian'), ('00000000-0000-0000-0000-000000000020', 'Jack'); INSERT INTO customer_phone (customer_id, phone_number) VALUES ('00000000-0000-0000-0000-000000000019', '000-3333-3333'), ('00000000-0000-0000-0000-000000000020', '000-3333-3333'); INSERT INTO family (id) VALUES ('00000000-0000-0000-0000-000000000004'); INSERT INTO family_member (family_id, customer_id) VALUES ('00000000-0000-0000-0000-000000000004', '00000000-0000-0000-0000-000000000019'), ('00000000-0000-0000-0000-000000000004', '00000000-0000-0000-0000-000000000020'); COMMIT;
MySQLのときは?
他手法との比較
- UNIQUE制約、排他制約やCHECK制約
- 原則として他テーブルを参照できない
- 外部キー制約
- 互換性を気にしなくてよく、すべて宣言的に収まるので、困りごとが起きないのであれば、こちらが望ましい
- 制約のために関連がいびつになったり、条件が柔軟だったり、変わるものだったりすると厳しい
- この方法は、SQLパズル の「パズル16 主任とアシスタント―参照整合性制約の正しい設定」を参照。