d.sunnyone.org: PostgreSQLの制約トリガー＋ビューを使ってテーブル跨ぎで整合性を保つ

RDBMSを使うと、UNIQUE制約やCHECK制約、排他制約などを利用して、指定した条件に沿う形で、データの整合性を保つことができる。しかし、「同じ電話番号を複数の顧客が使ってよいのは、同じ家族に所属している場合のみ」というような、制約の前提に他テーブルを使う（テーブルを跨ぐ）ケースは、よくでてくるわりに実現しにくい。この記事では、そのような制約をアプリケーションではなくPostgreSQLによって実現する方法を紹介する。

TL;DR

テーブル跨ぎの整合性を保つための手段として、制約トリガー（CONSTRAINT TRIGGER）がある。手順の例は以下の通り。

不整合を検出するビューを用意する
ビューに基づいて制約違反を判定する関数を定義する
制約トリガーを定義し、更新・挿入・削除時に整合性を検証する

前提（ユースケースの概要）

今回は以下のような顧客と家族の情報を例にする。

顧客は電話番号を持っている（こともある）
顧客は家族に所属している（こともある）
電話番号は原則として別の顧客と重複してはならない

同じ家族に所属する場合のみ重複することができる

電話番号も家族もあとから変更される

ER図は以下の通り。

手順

今回使うサンプルはすべて以下に置いてあるので、適宜動かせる。

https://github.com/sunnyone/constraint-trigger-exp

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　主任とアシスタント―参照整合性制約の正しい設定」を参照。

まとめ

本記事では、テーブル跨ぎで整合性を保つ例として、電話番号を共有して良いのは所属している家族同士のみというケースを用いて、制約トリガーの使い方を説明した。

同様の条件付き制約が必要な場面において、この手順が参考になれば幸いである。

d.sunnyone.org

ページ

2025-04-05

PostgreSQLの制約トリガー＋ビューを使ってテーブル跨ぎで整合性を保つ