複合 PostgreSQL 外部キーには、参照先の列を網羅する適格な一意キーが必要です。id だけを対象とする主キーは、その組み合わせを論理上は一意にする場合でも、(id, edition) への参照には適格ではありません。PostgreSQL: CREATE TABLE

このガイドでは、Mac の Terminal で作業する開発者がこの不一致を診断し、適切なリレーションシップを選び、node-postgres で結果をテストできるようにします。例に登場する行はすべて合成データの記事を表します。SQL は PostgreSQL 18 と通常の等価比較に基づく外部キーを対象とします。PostgreSQL 18 では、PERIOD を使う時間的参照について別個のルールが説明されています。PostgreSQL: CREATE TABLE

PostgreSQL が複合参照を拒否するのはなぜですか？

記事テーブルに id、edition、title の3列があるとします。主キーは id です。別のテーブルは、記事への参照を article_id と edition の2つの値で保持します。

意図するリレーションシップは、次のテーブル制約の断片で表されます。

FOREIGN KEY (article_id, edition)
  REFERENCES demo_articles (id, edition)

この宣言は、2つの値を組み合わせて1つの親行と照合するよう PostgreSQL に要求します。ドキュメントによると、参照先には、遅延不可の一意制約または主キー制約の列、あるいは非部分一意インデックスの列を指定する必要があります。既存の主キーが対象とするのは id だけですが、参照では (id, edition) が指定されています。PostgreSQL: CREATE TABLE

この指摘の背景にある数学的な考え方は理解できます。親行どうしが同じ id を持てないなら、同じ (id, edition) の組も持てないからです。それでも、その含意だけでは外部キー定義が求める、宣言済みの対応する参照先にはなりません。このスキーマでは、UNIQUE (id, edition) を追加すれば要件を満たせます。この追加制約は一意性の点では論理的に冗長ですが、複合参照先として具体的な役割を果たします。

次の親行を例に考えてみましょう。

id edition title
101 morning Paper Shapes
102 evening Color Notes

子の組 (101, 'morning') には一致する行があります。一方、組 (101, 'evening') にはありません。2つの値はテーブル内に存在しますが、異なる行にあります。両方の値が同じ記事を示す必要がある場合に複合リレーションシップを使う理由は、この違いにあります。

一意キーを追加すべきですか、それとも外部キーを簡素化すべきですか？

スキーマを変更する前に、必要なリレーションシップを選びます。考えるべきなのは、子が記事識別子と一緒に版も保持し、その整合性を強制する必要があるかどうかです。

必要な動作 適切な設計 結果
子が記事を特定し、現在の版を表示する article_id を保存し、親の id を参照する クエリ時に親から版を取得する
子が記事と一致すべき版を保存する 複合参照を維持し、親に UNIQUE (id, edition) を追加する PostgreSQL が保存された組が同時に存在することを検査する
同じ記事番号で複数の版行を識別する 親の識別子を (id, edition) としてモデル化する id だけを対象とする主キーでは、そのような親行を作れない

最後の行は別のデータモデルを示しています。エラーを消すためだけに単一列の主キーを削除しないでください。まず、記事番号の重複が本当に親テーブルに必要かを判断します。PostgreSQL では複合主キーを使えますが、テーブルに設定できる主キーは1つだけです。PostgreSQL: Constraints

以下の実行可能な例では、記事識別子は引き続き全体で一意です。子は意図的に版も保存するため、親は PRIMARY KEY (id) を維持し、さらにその組に対する一意キーを追加します。

Mac でエラーを再現して修正するにはどうすればよいですか？

既存のローカル PostgreSQL 18 データベースと、動作する Node.js のインストールを使います。この手順では、どちらもすでに利用できるものとします。Terminal を開き、新しいプロジェクトディレクトリを作成します。ドキュメントに記載されたパッケージ用コマンドでドライバーをインストールします。node-postgres: Welcome

mkdir composite-fk-demo
cd composite-fk-demo
npm init -y
npm install pg

次の完全なプログラムを demo.mjs として保存します。一時テーブルを作成し、拒否されるスキーマを示してから対応するキーを追加し、有効な記事の組と無効な記事の組をテストします。node-postgres ではトランザクションを同じクライアントインスタンスで実行する必要があるため、すべての文で1つのクライアントを使います。node-postgres: Transactions

ヘルパーは、予期される失敗ごとにセーブポイントを使い、トランザクションを継続できるようにします。PostgreSQL のドキュメントによると、ROLLBACK TO SAVEPOINT はそのセーブポイント以降のコマンドを取り消し、作業を再開できるようにします。PostgreSQL: ROLLBACK TO SAVEPOINT

import pg from 'pg';
const { Client } = pg;
const client = new Client();

const childDDL = `CREATE TEMP TABLE demo_article_links (
  link_id integer PRIMARY KEY,
  article_id integer NOT NULL,
  edition text NOT NULL,
  FOREIGN KEY (article_id, edition)
    REFERENCES demo_articles (id, edition)
)`;

async function expectError(sql, values, code) {
  await client.query('SAVEPOINT expected_error');
  try {
    await client.query(sql, values);
  } catch (error) {
    await client.query('ROLLBACK TO SAVEPOINT expected_error');
    await client.query('RELEASE SAVEPOINT expected_error');
    if (error.code !== code) throw error;
    console.log(`Expected error: ${code}`);
    return;
  }
  await client.query('ROLLBACK TO SAVEPOINT expected_error');
  await client.query('RELEASE SAVEPOINT expected_error');
  throw new Error(`Expected SQLSTATE ${code}, but query succeeded`);
}

try {
  await client.connect();
  await client.query('BEGIN');
  await client.query(`CREATE TEMP TABLE demo_articles (
    id integer PRIMARY KEY,
    edition text NOT NULL,
    title text NOT NULL
  )`);

  await expectError(childDDL, [], '42830');

  await client.query(`ALTER TABLE demo_articles
    ADD CONSTRAINT demo_articles_id_edition_key
    UNIQUE (id, edition)`);
  await client.query(childDDL);

  await client.query(`INSERT INTO demo_articles (id, edition, title)
    VALUES ($1, $2, $3), ($4, $5, $6)`,
    [101, 'morning', 'Paper Shapes', 102, 'evening', 'Color Notes']);

  await client.query(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [1, 101, 'morning']);

  await expectError(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [2, 101, 'evening'], '23503');

  const result = await client.query(`SELECT l.link_id, a.title, a.edition
    FROM demo_article_links AS l
    JOIN demo_articles AS a
      ON a.id = l.article_id AND a.edition = l.edition
    ORDER BY l.link_id`);
  console.table(result.rows);
} catch (error) {
  console.error(error.code ?? error.message);
  process.exitCode = 1;
} finally {
  try { await client.query('ROLLBACK'); }
  finally { await client.end(); }
}

プログラムは $1、$2 などのプレースホルダーを使って、ドライバーのパラメーター化クエリ API に従い、記事の値を個別に渡します。テーブル名と列名は SQL 内で固定されています。PostgreSQL のパラメーターを識別子の代わりに使うことはできません。node-postgres: Queries

既存のローカルデータベース用の接続設定を使ってプログラムを実行します。この実行例では、データベース名を article_demo、サーバーの待ち受けポートを 5432 とし、既存のデフォルトのローカル接続設定が使えるものとします。必要に応じてデータベース名やポートを変更してください。node-postgres は PostgreSQL の接続環境変数を読み込みます。node-postgres: Connecting

PGHOST=localhost PGPORT=5432 PGDATABASE=article_demo node demo.mjs

この記事の準備時に、JavaScript の構文チェックを行いました。データベースの動作はドキュメントに照らして確認しましたが、ここでは PostgreSQL サーバーに接続してプログラムを実行していません。

2つのエラーと最後のクエリから何がわかりますか？

前提条件が満たされていれば、次のメッセージが表示されます。

予期されるエラー: 42830
予期されるエラー: 23503

最初のエラーは、記事行を挿入する前に子テーブルを作成する段階で発生します。PostgreSQL は 42830 を invalid_foreign_key として識別します。親に対応する一意キーを追加すると、同じ子テーブル定義が有効になります。PostgreSQL: Error Codes

2番目のエラーは、(101, 'evening') を挿入するときに発生します。スキーマは有効ですが、指定された組を持つ親行がありません。PostgreSQL は 23503 を foreign_key_violation として識別します。エラーコードを確認すれば、ローカライズされたエラーテキストに頼らず、行の不一致と先ほどのスキーマの不一致を区別できます。PostgreSQL: Error Codes

最後のクエリは1行を返すはずです。リンク 1、タイトル Paper Shapes、版 morning です。結合条件にはリレーションシップの両方の要素が含まれています。これにより、制約で強制されるものと同じ組をクエリが表します。

外部キーを2つに分けると、異なるルールになります。edition にそれぞれ適格な一意キーがあったとしても、id と edition への個別の参照では、各値が異なる親行に一致することが許されます。実例ではその違いがはっきりします。拒否された組の各値は個別には存在しますが、組み合わせは存在しません。

最後の ROLLBACK は、テーブル定義も含め、デモ全体を意図的に破棄します。この設計を永続テーブルに適用する場合は、通常のマイグレーション手順でスキーマを変更してください。この一時テーブル用プログラムをコピーしても、アプリケーションのスキーマは永続化されません。

既存のスキーマを変更する前に何を確認すべきですか？

まず参照先の列リストを確認します。インデックス名や、現在の行がたまたま重複していないことに頼らず、その列リスト全体を対象とする親キーを特定します。この例で必要な追加は、プログラム内に示した UNIQUE (id, edition) という名前の制約です。

一意制約を追加すると、一意な B-tree インデックスが作成されます。複合設計と article_id だけを保存する設計のどちらを選ぶか判断する際は、このインデックスの追加も考慮してください。外部キー宣言によって子の列にインデックスが自動作成されることはありません。PostgreSQL: Constraints

既存の子テーブルに外部キーを追加する前に、保存されている組が親行と一致するか確認します。有効な参照先キーが満たすのはスキーマ上の要件です。子の不一致値を修正するものではありません。通常の ALTER TABLE ... ADD CONSTRAINT は既存行を検査します。PostgreSQL では外部キーを NOT VALID として追加し、後から VALIDATE CONSTRAINT を実行することもできます。これにより既存行の検査を後回しにしつつ、その後の挿入や更新には制約が適用されます。PostgreSQL: ALTER TABLE

検証と参照先の適格性は別の問題として扱ってください。NOT VALID を指定しても、不足している親キーは追加されません。また、一意性を証明せずに一意制約を追加する方法でもありません。PostgreSQL 18 では、このオプションを外部キー、チェック、NOT NULL の各制約に限定しています。PostgreSQL: ALTER TABLE

結果が変わる条件は何ですか？

部分一意インデックスは、ここで説明する通常の外部キーの参照先として適格ではありません。同様に、参照先の一意制約を遅延可能にしても、ドキュメントが求める遅延不可制約の要件は満たせません。この例では標準の UNIQUE (id, edition) を使います。PostgreSQL: CREATE TABLE

NULL の扱いも重要です。デフォルトの照合動作では、参照する列のいずれかが NULL の場合、複合外部キーは親行との一致を要求しません。この例では、保存されるすべての組に一致を必須とするため、子の両方の列に NOT NULL を指定しています。任意のリレーションシップを意図する場合、MATCH FULL を指定すると、すべて NULL の参照は許可され、部分的に NULL の参照は拒否されます。PostgreSQL: Constraints

確認すべきなのは組そのものです。子が、異なる行に属する記事識別子と版を指定できてしまうでしょうか。これを失敗させる必要があるなら、複合リレーションシップを維持し、親に対応するキーを用意します。子が記事を特定するだけでよいなら、id を参照し、クエリ時にその記事から版を取得します。

参考資料