RDBMSを使うと、UNIQUE制約やCHECK制約、排他制約などを利用して、指定した条件に沿う形で、データの整合性を保つことができる。しかし、「同じ電話番号を複数の顧客が使ってよいのは、同じ家族に所属している場合のみ」というような、制約の前提に他テーブルを使う（テーブルを跨ぐ）ケースは、よくでてくるわりに実現しにくい。この記事では、そのような制約をアプリケーションではなくPostgreSQLによって実現する方法を紹介する。
TL;DR
テーブル跨ぎの整合性を保つための手段として、制約トリガー（CONSTRAINT TRIGGER）がある。手順の例は以下の通り。
- 不整合を検出するビューを用意する
- ビューに基づいて制約違反を判定する関数を定義する
- 制約トリガーを定義し、更新・挿入・削除時に整合性を検証する
前提（ユースケースの概要）
今回は以下のような顧客と家族の情報を例にする。
- 顧客は電話番号を持っている（こともある）
- 顧客は家族に所属している（こともある）
- 電話番号は原則として別の顧客と重複してはならない
- 電話番号も家族もあとから変更される
ER図は以下の通り。
手順
今回使うサンプルはすべて以下に置いてあるので、適宜動かせる。
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のときは？
ビューは問題なく、pl/pgsqlのところは書き方を変えれば良さそうだが、CONSTRAINT TRIGGERがないので工夫が必要そう。
他手法との比較
- UNIQUE制約、排他制約やCHECK制約
- 外部キー制約
- 互換性を気にしなくてよく、すべて宣言的に収まるので、困りごとが起きないのであれば、こちらが望ましい
- 制約のために関連がいびつになったり、条件が柔軟だったり、変わるものだったりすると厳しい
- この方法は、SQLパズル の「パズル16 主任とアシスタント―参照整合性制約の正しい設定」を参照。
まとめ
本記事では、テーブル跨ぎで整合性を保つ例として、電話番号を共有して良いのは所属している家族同士のみというケースを用いて、制約トリガーの使い方を説明した。
同様の条件付き制約が必要な場面において、この手順が参考になれば幸いである。
sunnyone.org
