RDB: 外部キーの循環参照（相互参照）はありなのか - circular foreign key reference

RDB: 外部キーの循環参照（相互参照）はありなのか - circular foreign key reference

November 18, 2022

決済を行うシステムの場合、次のような仕様は珍しくないでしょう。

  • 顧客は 0 枚以上のクレジットカードを登録する。
  • 顧客が 1 枚以上のクレジットカードを登録している場合は、デフォルトで使用する１枚を指定する。

このデータを RDB で保持する方法を考える際に悩みました。

記載している SQL は PostgreSQL 準拠です。

パターン１：フラグを持たせる # 

CREATE TABLE customers(
  "id" uuid PRIMARY KEY,
  -- 以降顧客の情報
);

CREATE TABLE credit_cards(
  "id" uuid PRIMARY KEY,
  "customer_id" uuid NOT NULL,
  "is_default" boolean NOT NULL,
  -- 以降カードの情報

  FOREIGN KEY ("customer_id") REFERENCES customers ("id")
);

実装内容

  • クレジットカードにデフォルト使用されるものか否かのフラグを持たせる。
  • クレジットカードから顧客への外部参照を持たせる。

保証できない（アプリケーション側で保証する必要がある）こと

  • １人の customer に紐づく複数のクレジットカードの is_default が true になってしまう可能性。

パターン２：相互参照パターン # 

CREATE TABLE customers(
  "id" uuid PRIMARY KEY,
  "credit_card_id" uuid,
  -- 以降顧客の情報
);

CREATE TABLE credit_cards(
  "id" uuid PRIMARY KEY,
  "customer_id" uuid NOT NULL,
  -- 以降カードの情報

  FOREIGN KEY ("customer_id") REFERENCES customers ("id")
);

ALTER TABLE
  customers
ADD
  FOREIGN KEY ("credit_card_id") REFERENCES credit_cards ("id");

実装内容

  • 顧客からクレジットカードへの外部参照を持たせる。
  • クレジットカードから顧客への外部参照を持たせる。

保証できない（アプリケーション側で保証する必要がある）こと

  • customers の credit_card_id に他人の credit_card_id が登録される可能性。

パターン３：中間テーブルパターン # 

CREATE TABLE customers(
  "id" uuid PRIMARY KEY,
  -- 以降顧客の情報
);

CREATE TABLE credit_cards(
  "id" uuid PRIMARY KEY,
  "customer_id" uuid NOT NULL,
  -- 以降カードの情報

  FOREIGN KEY ("customer_id") REFERENCES customers ("id")
);

CREATE TABLE customer_default_credit_cards(
  "customer_id" uuid PRIMARY KEY,
  "credit_card_id" uuid NOT NULL,

  FOREIGN KEY ("customer_id") REFERENCES customers ("id")
  FOREIGN KEY ("credit_card_id") REFERENCES credit_cards ("id")
);

実装内容

  • 顧客とクレジットカードへの外部参照を持った中間テーブルを配置する。

保証できない（アプリケーション側で保証する必要がある）こと

  • customer_default_credit_cards の credit_card_id に他人の credit_card_id が登録される可能性。

循環参照はありか？ #

まず前置きとして。

「保証できないこと」に書いた内容は CHECK 制約などを用いることで保証できるものもあるかもしれませんが、ここでは対象外としています。

また、ここまでの整理にあたっては以下の記事を参考にしました。

そして、外部キーの循環参照についてです。「パターン２」で発生している物ですね。

今回の場合は、循環参照の中でも互いに参照し合う形となっているため、相互参照パターンと書きました。

循環参照・相互参照はなんとなくよろしくないものかと感じていたのですが、絶対に避けなければいけないというものでもないようですね。

これは上記に貼った記事内での同様の風潮ですし、“circular reference” みたいなワードで検索してみると次のような記事を見つかります。

避けるべきという人もいる一方で、必然的にそうなるのであれば問題ないという人もいます。

これまでに記載したパターンでは customers の credit_card_id は NULLABLE でしたが、これが NOT NULL になったとします。

具体的には「顧客は必ず最低 1 枚のクレジットカードを登録する」という仕様だった場合です。

このような相互に NOT NULL な参照は INSERT ができないため相互参照は避けなければならない、という理由も述べられていますが、すくなくとも現在は遅延制約（DEFERRED）が使用できるため問題とはなりません。

ここまで考えてみると、循環参照は私が思い込んでいたほどには問題ではないのだと思われます。