PostgreSQLのNOT VALID外部キーは、既存行の初回チェックを省略しながら、新しい参照を検証します。以前からある不一致行は、修正して検証するまで残ります。PostgreSQL 17: ALTER TABLE

このガイドは、Terminalを使うMac上の開発者を対象にしています。データが入っているテーブルに外部キーを追加し、その結果を理解したい場合に役立ちます。node-postgresを使った完全な例では、合成した記事データのみを使用し、制約の作成から修正、検証の成功まで、不一致の参照を1つ追跡します。

既存行と新しい書き込みには何が起きますか？

2つのテーブルを考えてみましょう。articlesには記事IDとタイトルが格納され、article_linksにはそれらのIDへの参照が格納されます。リンクのarticle_idがnullではなく、対応する記事がない場合、そのリンクは不一致です。外部キーをNOT VALIDで追加すると、以前からある不一致は残したまま、それ以降の書き込みに制約を適用できます。PostgreSQL 17: ALTER TABLE

制約追加後の操作 この例での結果
以前からある不一致リンクをそのままにする 保存されたまま残る
既存の記事を参照するリンクを挿入する 成功する
存在しない記事を参照するリンクを挿入する 失敗する
リンクの記事IDを別の存在しないIDに変更する 失敗する
不一致リンクが残っている状態で検証する 検証に失敗する
不一致の参照をすべて修正してから検証する 検証に成功する可能性がある

この結果は、制約の追加と検証が別々に行われるという、ドキュメントに記載された動作に基づきます。PostgreSQL 17: ALTER TABLE

重要な例外として、外部キーの値を変えない更新があります。PostgreSQL 18では、古いキーと新しいキーが等しい場合、実装が外部キーのチェックを省略することがあります。そのため、以前からある不一致リンクのラベルだけを変更すれば、参照を修正しなくても更新に成功する場合があります。また、実装には現在のトランザクションで挿入された行に対する特別扱いもあるため、あらゆる更新手順に当てはまるとは限りません。PostgreSQL 18 foreign-key implementation

実際には、ラベルの更新に成功しても、クリーンアップが完了した証拠にはなりません。不一致の参照を直接調べ、テーブル全体で制約が成り立つことを検証して確認してください。

どのPostgreSQLバージョンとテーブル構成が必要ですか？

以下の例は、通常の非パーティションテーブルを使い、PostgreSQL 17と18を対象にしています。パーティションテーブルには重要なバージョン上の違いがあります。PostgreSQL 17では、パーティションテーブルにNOT VALIDとして外部キーを宣言することが明示的に禁止されています。PostgreSQL 18では、この機能が導入されました。インストール済みのNodeパッケージから対応状況を推測せず、サーバーのバージョンを確認してください。PostgreSQL 17: ALTER TABLE、PostgreSQL 18 release notes

PostgreSQL 17で参照元がパーティションテーブルの場合、サポートされる直接的な方法は、参照を修正してから通常どおり外部キーを追加し、初回の検証スキャンを実行させることです。パーティション親テーブル上で段階的に検証する必要がある場合は、PostgreSQL 18でその機能が提供されています。このチュートリアルでは、パーティション階層の詳細で行の動作がわかりにくくならないよう、シンプルなテーブル構成を使います。

参照先キーには、適切な一意性の保証も必要です。ここでは、articles.article_idが主キーです。参照元の列では、意図的にNULLを許可しています。デフォルトのMATCH SIMPLEルールでは、nullの参照に一致する記事は必要ありません。すべてのリンクで記事の指定を必須にする場合は、別途NOT NULLを追加してください。PostgreSQL 18: CREATE TABLE

Macから合成データの完全な例を実行する

PostgreSQLが稼働していること、既存のローカル接続設定が機能すること、そしてnode-postgresがサポートするバージョンのNode.jsが必要です。Terminalでプロジェクトディレクトリに移動し、以下のコマンドでパッケージをインストールしてから、完全なプログラムをnot-valid-demo.mjsとして保存してください。node-postgresの公式入門ドキュメントに、パッケージのインストール方法が記載されています。node-postgres documentation

npm install pg

このプログラムはnew pg.Client()を使います。これは、PGHOST、PGPORT、PGDATABASE、PGUSERなどのPostgreSQL接続用環境変数を読み込みます。ローカルデータベースへの接続にすでに使っている設定を指定してください。node-postgres: Connecting

2つのテーブルは一時テーブルで、この接続のセッション内でのみ存在します。PostgreSQLでは、一時テーブルの外部キーが一時テーブルを参照する必要があるため、プログラムでは両方のテーブルを同時に作成します。PostgreSQL 18: CREATE TABLE

各操作は個別のawait付きクエリで実行します。値の受け渡しには$1や$2などのnode-postgresパラメーターを使い、テーブル名と制約名はSQL内で固定しています。node-postgres: Queries

import pg from 'pg';

const client = new pg.Client({ connectionTimeoutMillis: 5000 });
const validate = `ALTER TABLE pg_temp.article_links
  VALIDATE CONSTRAINT article_links_article_fk`;

async function expectForeignKeyError(label, sql, values = []) {
  try {
    await client.query(sql, values);
  } catch (error) {
    if (error.code !== '23503' ||
        error.constraint !== 'article_links_article_fk') throw error;
    console.log(label + ': 23503 (expected)');
    return;
  }
  throw new Error(label + ': unexpectedly succeeded');
}

async function showStatus(label) {
  const result = await client.query(`
    SELECT conname, convalidated
    FROM pg_constraint
    WHERE conrelid = 'pg_temp.article_links'::regclass
      AND conname = 'article_links_article_fk'
      AND contype = 'f'
  `);
  console.log(label, result.rows);
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows);
  await client.query(`CREATE TEMP TABLE articles (
    article_id integer PRIMARY KEY,
    title text NOT NULL
  )`);
  await client.query(`CREATE TEMP TABLE article_links (
    link_id integer PRIMARY KEY,
    article_id integer,
    label text NOT NULL
  )`);
  await client.query(
    'INSERT INTO pg_temp.articles VALUES ($1, $2)',
    [1, 'Desk arrangement']
  );
  await client.query(`INSERT INTO pg_temp.article_links VALUES
    (10, 1, 'Matching article'),
    (11, 99, 'Older unmatched reference')`);
  await client.query(`ALTER TABLE pg_temp.article_links
    ADD CONSTRAINT article_links_article_fk
    FOREIGN KEY (article_id)
    REFERENCES pg_temp.articles (article_id) NOT VALID`);
  await showStatus('After adding');

  await expectForeignKeyError(
    'New unmatched reference',
    'INSERT INTO pg_temp.article_links VALUES ($1, $2, $3)',
    [12, 99, 'New reference']
  );
  await expectForeignKeyError(
    'Changed unmatched reference',
    'UPDATE pg_temp.article_links SET article_id = $1 WHERE link_id = $2',
    [98, 11]
  );
  await client.query(
    'UPDATE pg_temp.article_links SET label = $1 WHERE link_id = $2',
    ['Revised label', 11]
  );
  console.log('Label-only update succeeded');

  const unmatched = await client.query(`
    SELECT l.link_id, l.article_id, l.label
    FROM pg_temp.article_links AS l
    WHERE l.article_id IS NOT NULL
      AND NOT EXISTS (
        SELECT 1 FROM pg_temp.articles AS a
        WHERE a.article_id = l.article_id
      )
    ORDER BY l.link_id
  `);
  console.log('Unmatched references', unmatched.rows);
  await expectForeignKeyError('Validation before repair', validate);
  await showStatus('After failed validation');

  await client.query(
    'UPDATE pg_temp.article_links SET article_id = $1 WHERE link_id = $2',
    [1, 11]
  );
  await client.query(validate);
  await showStatus('After successful validation');
} finally {
  await client.end();
}

保存したプログラムを、同じプロジェクトディレクトリから実行します。

node not-valid-demo.mjs

準備時に、JavaScriptの構文チェックは通過しました。調査環境ではデータベースを実行できなかったため、以下の結果は実行ログから取得したものではなく、ドキュメントに記載された動作から予想されるものです。

結果をどう解釈すればよいですか？

まず、制約のconvalidatedがfalseに設定されているはずです。カタログでは、このフィールドは制約が検証済みかどうかを示します。制約名はデータベース全体で一意とは限らないため、クエリではテーブル名と制約名の両方で絞り込みます。PostgreSQL: pg_constraint

次に、不一致参照を持つ新しいリンクの挿入と、不一致参照への変更は、それぞれ23503を返すはずです。PostgreSQLでは、このSQLSTATEをforeign_key_violationと定義しています。また、ローカライズされたメッセージ文字列との照合ではなく、エラーコードを確認することを推奨しています。このヘルパーは、想定されたコードと制約名だけを受け入れ、それ以外の予期しないエラーが発生するとプログラムを停止します。PostgreSQL error codes

ラベルだけを更新しても、リンク11は記事99を参照したままです。そのため、診断クエリはラベルがRevised labelに変わったそのリンクだけを返すはずです。明示的なIS NOT NULL条件で任意のnull参照を除外し、NOT EXISTSでarticlesに存在しないIDを特定します。これらは、提示した合成データから得られる例示的な結果です。

この時点での検証は失敗するはずです。修正によってリンク11が記事1を参照するようになった後は、検証に成功し、convalidatedがtrueに変わるはずです。VALIDATE CONSTRAINTはテーブルをスキャンして、未検証の制約を検証します。修正自体は行いません。PostgreSQL: ALTER TABLE

このデモでの修正は、編集上の選択として明示的に行っています。どちらのリンクもDesk arrangementを参照する想定です。ご自身の記事データで不一致の参照が見つかったら、IDを修正するか、不足している記事を追加するか、古いリンクを削除するかを判断する前に、各参照を確認してください。不一致のIDをすべて自動的に1つの記事へ転送すると、データベースでは判別できない違いが隠れてしまいます。

データが入っているテーブルにこの手順を適用するには？

3つの段階に分けて進めます。NOT VALID制約を追加してコミットし、不一致の参照を調べて修正し、その後に検証します。外部キーの追加では、両方のテーブルにSHARE ROW EXCLUSIVEロックがかかります。検証では、参照元テーブルにSHARE UPDATE EXCLUSIVE、参照先テーブルにROW SHAREロックがかかります。これにより、検証スキャン中も通常の更新を並行して実行できます。ただし、ロックの取得を待つことはあります。PostgreSQL: ALTER TABLE

最初の追加と後続の検証を別々のトランザクションにすると、段階的な方法の目的を保てます。つまり、時間のかかるスキャンを開始する前に、最初のトランザクションを完了できます。追加は短時間の操作として計画し、診断クエリをクリーンアップの補助に使って、検証の成功を完了の目安にしてください。

このデモでは、予想される各ステートメントの失敗を個別に処理できるよう、明示的なトランザクションブロックを使っていません。この手順をnode-postgresのトランザクションに応用する場合は、BEGIN、COMMIT、ROLLBACKを自分で発行し、すべてのステートメントを同じクライアントで実行してください。トランザクションのステートメントにpool.query()を使わないよう、node-postgresのドキュメントに明記されています。node-postgres: Transactions

この例の3つの観察結果を合わせて判断してください。不一致行で修正が必要な箇所を特定し、新しい参照が拒否されることで制約の継続的な適用を確認し、convalidated = trueで検証の成功を記録します。これにより、記事データが入っているテーブルに関連付けを追加する際の、明確な完了点が得られます。

参考資料