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

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

 TL;DR

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

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

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

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

• 顧客は電話番号を持っている（こともある）
• 顧客は家族に所属している（こともある）
• 電話番号は原則として別の顧客と重複してはならない
• 同じ家族に所属する場合のみ重複することができる
• 電話番号も家族もあとから変更される 

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.の行の組み合わせを生成する
3. 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　主任とアシスタント―参照整合性制約の正しい設定」を参照。
  

まとめ

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

 

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

linariaからPanda CSSに移行した

うちのサイトは今のところNext.jsで書いてあるのだけど、スタイリングにはlinariaを使っていた。次の作業のためにPanda CSSに置き換えたので、その記録を残しておく。

 Panda CSSの特徴

移行時のポイントの前に、気にしておくべき造りの特徴としていくつかあげておく。

object の形でのスタイル指定 

linariaではstyled.div`` やcss``の指示でCSS文字列を記述できるが、Panda CSSではjs/tsのオブジェクトの形で指定する。最近ではよくある形。

const appItemsDivStyle = css({
    display: 'grid',
    gridTemplateColumns: '1fr',
    gap: '15px',

    md: {
        gridTemplateColumns: '1fr 1fr',
    }
});

基本的にはCSSの構造そのものを書くが、Chakraのような記法を受け付けているところもある。ブレイクポイントやデザイントークンのあたり。このへんにどう乗るかは移行時に気にかけておくと良いと思う。

クラスが細かく分割される（Atomic CSSスタイル）

linariaでは基本的にはスタイル指定の部分はまとまったままで、クラス名が生成されて適用される。このようなイメージ。

.sxatkf1 {
  padding-bottom:5px;
  border-bottom:1px solid #012356;
  margin-bottom:20px;
  line-height:1.8;
  font-size:20px;
  font-weight:700
}

classへの指定はこんなかんじ。

<h2 class="mocked-styled-0 sxatkf1">

一方で、Panda CSSではもっと細かく分解される。適当に切り出すとこんな感じ。

    .md\:fs_16px {
      font-size: 16px;
}

    .md\:fs_24px {
      font-size: 24px;
}

    .md\:mt_30px {
      margin-top: 30px;
}

分解されたスタイルは、クラス名のほうで合体される。

<h2 class="pb_5px bd-b_1px_solid_{colors.accent} mb_20px lh_1.8 fs_20px fw_bold md:fs_24px">

今回は問題にならなかったが、classがひとつであることに依存したような造りにもしなっているのであれば注意が必要。

カスケードレイヤー (@layer) を使っている

Cascade Layers  を参照。Panda CSSのみのときはたいして気にならないが、組み合わせるときに注意が必要。

バンドラの外でTypeScript ASTからスタイルを抽出する

zero-runtime CSS-in-JSのひとつのやりかたとして、linariaのようにwebpackなんかのバンドラの処理に手を入れる形でスタイル出力の変換をする形があるが、Panda CSSはts-morphを使ってバンドラの外でTypeScript ASTをparseする形でスタイルを抽出・出力する。

これはviteだのturbopackだのの話に巻き込まれにくいというメリットがあるが、linariaとは異なるタイプの制限があるので、知っておく必要がある。詳しくはマニュアルのDynamic stylingを参照。

eslint pluginによる静的検証がある

公式にeslint pluginがある（@pandacss/eslint-plugin）ので、TypeScriptの型チェックが及ばない部分、例えば動的な変数や変なトークン名なんかはeslint pluginによって検証できる。

zero-runtime らしい制約をlintでチェックしてくれるのは助かる。

移行時のポイント

上記を踏まえつつ、移行時に役に立ったり、ひっかかったりしたポイントを書く。

記述はLLMでだいたい移行できた

linariaからpandacssへの移行をお願いするだけで、だいたい書き直してくれた。後述のkeyframeを移したり、tokenに書き換えたりするところはできなかったので、手動で対応した。

ChatGPTならgpt4o-miniでだいたい行けたし、ollama + phi-4でも書き換えられた（めちゃ遅いけど）。

今回はstyled指定よりcss指定のほうがよく、diffの都合その場のほうがよかったりした（classNameのほうに移そうとしてくれたりしなかったりする）ので、そのあたりは指示した。

変数参照は基本できない→tokenを使う

はまったところとして、importした変数を参照できないので、tokenに置き換える必要がある。Design Tokensを参照。

これは、eslint pluginのno-dynamic-styling (recommendedに入っている) でわかるので、潰していけば良い。

lintでなんとかなったが、panda debugも知っておくと良いかも。

resetを持っている 

pandaは自身でresetを持っている。

もともとが雑だったので、今回はpandaのresetに乗るようにしたが、 preflight オプションでオフにできそうなので、このあたりも見ると良さそう。

素のstyleはlayerに気をつける

もともとaのようにグローバルにスタイルをつけていたのだけど、移行したらそっちが勝ちすぎたので、その部分に@layerの指示を入れた。今回はCascading Layersの記述の通り、@layer resetを指定。

keyframesの定義はconfigに

keyframesはその場に定義できないので、configに移した。configに書く設定はいくつかあるので気をつける。

eslint設定はカスタマイズが必要そう

基本的には@pandacss/recommendedで良いと思うのだけど、今回は以下を足した。

    "@pandacss/no-hardcoded-color": "off",
    "@pandacss/no-unsafe-token-fn-usage": "off"

no-hardcoded-colorは移植の都合。no-unsafe-token-fn-usageは'{sizes.foo}'ってわざわざ書かなくても'foo'でいいところはそうしましょう、みたいなオプションなのだけど、{sizes.foo} みたいに書くとno-invalid-token-pathsが機能しやすいので無効にして{}やtoken()を積極的に使っていくことにした。

記述の妥当性のほかにも、好みの指定もできて、prefer-longhand-propertiesなんかは役に立ちそう。no-margin-propertiesをCSS-in-JSが持っているのはちょっと面白い。

おわりに

思ったより考えてあるなって思った。ひっかかるポイントはあるけど、もうちょっと使ってみようと思う。