記事IDが2つの参照列のどちらにも重複して現れないようにするには、両方の役割を、記事IDに一意制約を設けた参照テーブルに格納します。各行で値が入る列が必ず1つだけなら、一意式インデックスを使うほうが簡単です。

このチュートリアルは、PostgreSQLとnode-postgresを使ってmacOSのTerminalで作業する開発者を対象としています。目的は、記事参照を1つの共有プールとして扱うことです。記事がどちらかの役割で参照されたら、その記事への別の参照は失敗しなければなりません。サンプルの行はすべて架空のものです。

どちらの列でも一意であるとは、どういう意味ですか？

両方の列が同じ記事カタログを参照するとします。意図するルールは、2つの位置が同じ行にある場合も含め、すべての非NULLの記事IDが全参照位置を通じて最大1回しか現れないことです。これは、各列を個別に一意にするよりも強い制約です。

バンドル 主記事 副記事 意図するルールでの結果
1 101 102 許可
2 103 101 拒否：101はすでにバンドル1にある
3 104 104 拒否：同じ記事が両方の位置を占めている

複合UNIQUE (primary_article_id, secondary_article_id)は組み合わせを一意にします。個別の一意制約は、各列をそれぞれ一意にします。どちらの設計でも、バンドル1と2の間で列をまたいで値が重複するケースは拒否されません。PostgreSQLのドキュメントによると、複数列の一意インデックスは、インデックス対象のすべての値が一致すると重複を拒否します。一意インデックス

INSERTクエリを書く前に、許可される行の形に合わせて設計を選びます。

必要な行の形 適した設計
両方の参照に値が入る場合がある 参照を行として格納し、article_idを一意にする
必ず1つだけ参照に値が入る 行チェックでその条件を強制し、COALESCEに一意インデックスを設定する
参照に値が入らない場合、または1つだけ入る場合がある 最大1つであることをチェックし、同じ式インデックスを使う
列が別々のカタログを参照する 数値IDが同じ場合に同じものを表すか、まず定義する。このチュートリアルでは1つの共有カタログを前提とする

2つの参照を許可する場合、ルールをどう強制しますか？

各参照を、バンドル、役割、記事IDを含む行として表します。役割を保持することで、元の列の意味が維持されます。article_idに対する単一の一意制約で、両方の役割を対象にできます。

以下のスキーマでは、PostgreSQLの標準的な制約を組み合わせています。外部キーは対応するカタログ行の存在を求め、複合主キーはバンドルとスロットの組み合わせごとに1つの参照だけを許可し、名前付きの一意制約は各記事を1回だけ許可します。これらの制約の仕組みは制約で説明されています。

再現可能なサンプルを作成する

PostgreSQL 14～18と利用可能なpsqlコマンドがある、空のローカルサンプルデータベースを使います。以下のセットアップ全体をschema.sqlとして保存してください。通常の非パーティションテーブルを使用し、拡張機能は必要ありません。

BEGIN;

CREATE TABLE articles (
    article_id integer PRIMARY KEY,
    title text NOT NULL
);

CREATE TABLE article_bundles (
    bundle_id integer PRIMARY KEY,
    label text NOT NULL
);

CREATE TABLE bundle_refs (
    bundle_id integer NOT NULL
        REFERENCES article_bundles (bundle_id),
    slot text NOT NULL
        CHECK (slot IN ('primary', 'secondary')),
    article_id integer NOT NULL
        REFERENCES articles (article_id),
    PRIMARY KEY (bundle_id, slot),
    CONSTRAINT bundle_refs_article_once UNIQUE (article_id)
);

INSERT INTO articles (article_id, title) VALUES
    (101, 'Grid spacing'),
    (102, 'Type scale'),
    (103, 'Page margins'),
    (104, 'Caption layout'),
    (105, 'Colour palette');

INSERT INTO article_bundles (bundle_id, label)
VALUES (1, 'Opening notes');

INSERT INTO bundle_refs (bundle_id, slot, article_id) VALUES
    (1, 'primary', 101),
    (1, 'secondary', 102);

COMMIT;

macOSのTerminalで、PGDATABASEに既存のサンプルデータベース名を設定してから、このファイルを実行します。ローカルの接続設定がすでに機能していることを前提としています。

export PGDATABASE=article_demo
psql -v ON_ERROR_STOP=1 -f schema.sql

この設計では、バンドルに参照が0個、1個、または2個ある状態を許可します。一意性と役割の最大数2は強制しますが、すべてのバンドルに両方の役割があることまでは求めません。それは別の要件です。参照がない場合は、行を作成しません。

読み取り時には2列の形を保つ

アプリケーションは引き続き、2つの参照フィールドを持つバンドルごとに1行を取得できます。セットアップ後に次のクエリを実行してください。

SELECT b.bundle_id,
       b.label,
       p.article_id AS primary_article_id,
       s.article_id AS secondary_article_id
FROM article_bundles AS b
LEFT JOIN bundle_refs AS p
  ON p.bundle_id = b.bundle_id AND p.slot = 'primary'
LEFT JOIN bundle_refs AS s
  ON s.bundle_id = b.bundle_id AND s.slot = 'secondary'
ORDER BY b.bundle_id;

最初の結果は、参照が101と102のバンドル1です。スキーマの主キーにより、各結合でスロットごとに参照が最大1つだけ見つかります。参照値の2つ目のコピーを維持する代わりに、このクエリの結果形式をアプリケーションの読み取り形式として使ってください。

node-postgresで両方の参照を挿入するには？

バンドルとその参照を1つのトランザクションで作成します。参照のいずれかが競合した場合は、バンドルもロールバックします。node-postgresでは、トランザクション内のすべての文に同じチェックアウト済みクライアントを使う必要があります。この一連の処理には、pool.query()を個別に呼び出す方法は適していません。トランザクション

ローカルのプロジェクトディレクトリで、ドキュメントに記載されたpgパッケージをインストールします。現行のNode.js LTSリリースを使い、以下のプログラムをdemo.mjsとして保存してください。SQLのセットアップ後、同じ接続環境で実行します。node-postgresのドキュメントには、インストールと環境変数による接続の両方が記載されています。

npm install pg
node demo.mjs

import pg from 'pg';

const { Pool } = pg;
const pool = new Pool();

async function addBundle(bundleId, label, primaryId, secondaryId) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    await client.query(
      'INSERT INTO article_bundles (bundle_id, label) VALUES ($1, $2)',
      [bundleId, label]
    );

    await client.query(
      `INSERT INTO bundle_refs (bundle_id, slot, article_id)
       VALUES ($1, 'primary', $2), ($1, 'secondary', $3)`,
      [bundleId, primaryId, secondaryId]
    );

    await client.query('COMMIT');
    console.log(`Added bundle ${bundleId}`);
  } catch (error) {
    await client.query('ROLLBACK');

    if (
      error.code === '23505' &&
      error.constraint === 'bundle_refs_article_once'
    ) {
      console.log(
        `Bundle ${bundleId} rejected: an article is already referenced`
      );
      return;
    }

    throw error;
  } finally {
    client.release();
  }
}

try {
  await addBundle(2, 'Layout notes', 103, 104);
  await addBundle(3, 'Colour notes', 105, 101);
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await pool.end();
}

このプログラムは、node-postgresのクエリAPIに従い、番号付きパラメーターと別個の値配列を使って値を渡します。初回実行時に想定される出力は次のとおりです。

バンドル2を追加しました
バンドル3を拒否しました：記事はすでに参照されています

2つ目のトランザクションは記事101の再利用を試みます。ロールバックによってバンドル3が削除され、記事105は未参照のままになります。重複メッセージが表示されるのは、名前を指定した一意性競合の場合だけです。それ以外のエラーは呼び出し元に伝播します。PostgreSQLでは一意性違反をSQLSTATE 23505で識別し、制約名はメッセージ本文とは別に提供します。PostgreSQLエラーコード

先ほどの読み取りクエリをもう一度実行して結果を確認します。バンドル1と2が残り、参照は101～104となるはずです。このデモは新規セットアップごとに1回実行するものとして扱ってください。プログラムを再実行すると、既存のバンドル2の主キーにぶつかります。

COALESCEの一意インデックスで十分なのはどんな場合ですか？

この小規模な設計は、行に実際の参照が最大1つしか含まれない場合に限って使います。COALESCEは最初の非NULL引数を返すため、最初の列に値があれば、インデックス対象の式では2つ目の列が無視されます。また、PostgreSQLでは、引数の型が共通の型に解決される必要があります。条件式

たとえば、行(101, 102)と(103, 101)からは、式の値として101と103が生成されます。これらの式の値に一意インデックスを設定すると、記事101が2回現れていても両方の行が受け入れられます。この反例は、式に行の形の制約が必要な理由を示しています。

必ず1つだけ参照を持つルールの場合は、先に作成したarticlesカタログを用意したうえで、次の別例を実行します。ここでは別のテーブルを使って別のスキーマを示します。bundle_refsに参照を追加するものではありません。

CREATE TABLE single_article_refs (
    reference_id integer PRIMARY KEY,
    primary_article_id integer REFERENCES articles (article_id),
    secondary_article_id integer REFERENCES articles (article_id),
    CONSTRAINT exactly_one_article CHECK (
        (primary_article_id IS NULL)
        <> (secondary_article_id IS NULL)
    )
);

CREATE UNIQUE INDEX single_article_refs_article_once
ON single_article_refs (
    (COALESCE(primary_article_id, secondary_article_id))
);

INSERT INTO single_article_refs
    (reference_id, primary_article_id, secondary_article_id)
VALUES (1, 101, NULL);

INSERT INTO single_article_refs
    (reference_id, primary_article_id, secondary_article_id)
VALUES (2, NULL, 101)
ON CONFLICT (
    (COALESCE(primary_article_id, secondary_article_id))
)
DO NOTHING
RETURNING reference_id;

このチェックは、NULLではない2つのBoolean値を比較し、一方の列がNULLで他方に値がある場合にのみ許可します。PostgreSQLでは、同じ行の複数列を対象とするチェックがサポートされています。PostgreSQL 14の制約

一意式インデックスにより、値が入っている方の列の値を共通の比較対象にできます。式インデックスは、現行のPostgreSQLとPostgreSQL 14の両方のドキュメントに記載されています。この方法には、PostgreSQL 15で追加されたNULLの扱いに関する構文は必要ありません。

2つ目のINSERTは、記事101がすでに参照されているため、行を返しません。式はON CONFLICTの対象にでき、RETURNINGは実際に挿入または更新された行を返します。結果が空なら、挿入はスキップされたと判断します。式インデックスの名前は、ON CONFLICT ON CONSTRAINTで指定するテーブル制約名ではありません。INSERT

両方の列が空でもよい場合は、テーブル定義時に、必ず1つというチェックを次の行レベル条件に置き換えます。式インデックスはそのまま使います。PostgreSQLのデフォルトの一意インデックスにおけるNULLの扱いでは、両方がNULLの行を複数許可します。一意インデックス

-- Alternative CHECK fragment for the CREATE TABLE definition:
CHECK (
    primary_article_id IS NULL
    OR secondary_article_id IS NULL
)

既存テーブルを変更する前に重複を診断するには？

UNION ALLを使って両方の列を1つのストリームに展開し、記事IDでグループ化します。重複を保持することが重要です。通常のUNIONでは、見つける必要のある重複の証拠が取り除かれることがあります。この診断用SQLは架空の入力を使い、上記のスキーマとは独立して実行できます。

WITH legacy_refs (bundle_id, primary_article_id, secondary_article_id) AS (
    VALUES
        (1, 101, 102),
        (2, 103, 101),
        (3, 104, 104)
),
expanded AS (
    SELECT bundle_id, 'primary' AS slot,
           primary_article_id AS article_id
    FROM legacy_refs
    WHERE primary_article_id IS NOT NULL

    UNION ALL

    SELECT bundle_id, 'secondary' AS slot,
           secondary_article_id AS article_id
    FROM legacy_refs
    WHERE secondary_article_id IS NOT NULL
)
SELECT article_id,
       count(*) AS occurrences,
       string_agg(
           bundle_id::text || ':' || slot,
           ', ' ORDER BY bundle_id, slot
       ) AS locations
FROM expanded
GROUP BY article_id
HAVING count(*) > 1
ORDER BY article_id;

想定される重複IDは101と104で、それぞれ2回出現します。既存テーブルを調べる場合は、最初のCTEを実際の列に置き換えます。移行前に報告されたすべての出現箇所を確認してください。行を自動削除すると、意図した参照まで失われる可能性があります。すべての参照を選択した形式に移し、その後の書き込み先をそこに統一します。サンプルの別々の代替テーブル同士では、一意性が共有されないためです。

最終的な判断をデータベースで強制する必要があるのはなぜですか？

事前の検索で既存の競合を説明することはできますが、それだけでは一意性を保証できません。2つの処理が、どちらかが挿入する前に同じ未使用の参照を確認する可能性があります。PostgreSQLの一意性実装では、インデックス挿入時に競合を確認し、必要に応じて競合する未コミットのトランザクションを待ってから、結果を再確認します。インデックスの一意性チェック

他の行を検索するCHECKで、この仕組みを置き換えないでください。PostgreSQLでは、別の行にまたがるチェックを継続的に維持される保証としてサポートしていないことが明記されています。許可される列の形には行チェックを使い、参照の重複には一意制約または一意インデックスを使います。制約

JavaScriptプログラムは構文チェックに合格し、反例と「必ず1つ」の真理値表は決定的に検証しました。PostgreSQLの例はドキュメントと照合して確認しましたが、この調査環境ではデータベースに対して実行していません。

2つの参照位置にどちらも値が入る場合は、参照テーブルの設計によって、記事IDの一意制約1つでこのルールを直接強制できます。列のどちらか一方だけを選ぶ場合は、チェック付きのCOALESCE設計を使うと、より小さなスキーマにできます。許可される行の形に合わせて選び、最後の一意性判断はデータベースに任せてください。

出典