若要避免文章 ID 在兩個參照欄位中的任一欄位重複出現，請將兩種角色都放入一個參照資料表，並讓文章 ID 唯一。若每一列恰好只允許一個欄位有值，唯一運算式索引會是更簡單的選擇。

本教學適用於在 macOS Terminal 中使用 PostgreSQL 和 node-postgres 的開發者。目標是強制使用同一個文章參照集合：文章一旦以任一角色被參照，就不得再建立該文章的其他參照。所有範例資料列都是合成資料。

在任一欄位中唯一是什麼意思？

假設兩個欄位都參照同一個文章目錄。預期規則是：每個非 null 的文章 ID 在所有參照位置中最多出現一次，包括同一列中的兩個位置。這比個別限制每個欄位唯一更嚴格。

套件 主要文章 次要文章 依預期規則的結果
1 101 102 接受
2 103 101 拒絕：101 已出現在套件 1 中
3 104 104 拒絕：同一篇文章占據兩個位置

複合 UNIQUE (primary_article_id, secondary_article_id) 會讓組合保持唯一。分別設定唯一限制則會讓每個欄位各自唯一。這兩種設計都不會拒絕套件 1 和 2 之間跨欄位重複的情況。PostgreSQL 文件說明，多欄位唯一索引會在所有索引值都相同時拒絕重複項目。唯一索引

在撰寫 INSERT 查詢之前，先根據允許的資料列形狀選擇設計：

必要的資料列形狀 適用的設計
兩個參照都可以有值 將參照儲存為資料列；讓 article_id 唯一
必須恰好有一個參照有值 以資料列檢查限制恰好一個欄位有值；為 COALESCE 建立唯一索引
可以有零個或一個參照有值 使用至多一個欄位有值的檢查，以及相同的運算式索引
欄位參照不同的目錄 先定義相同的數字 ID 是否代表相同項目；本教學假設使用同一個共用目錄

兩個參照都允許有值時，如何強制規則？

將每個參照表示為一筆資料列，其中包含套件、角色和文章 ID。角色欄位保留原始欄位的意義。接著，對 article_id 設定單一唯一限制，即可涵蓋這兩種角色。

以下結構描述結合了 PostgreSQL 的一般限制：外鍵要求目錄中有對應資料列，複合主鍵允許每個套件和位置有一筆參照，而具名唯一限制則要求每篇文章只能出現一次。這些限制機制詳見限制。

建立可重現的範例

使用空白的本機範例資料庫、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

此設計允許套件有零個、一個或兩個參照。它會強制唯一性，並限制最多只能有兩種角色；但不要求每個套件都必須具有兩種角色。那是另一項需求。沒有參照時，以缺少資料列表示。

保留雙欄位形狀供讀取

應用程式仍可用兩個參照欄位擷取每個套件一筆資料列。在完成設定後執行此查詢：

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;

初始結果是套件 1，參照文章 101 和 102。結構描述中的主鍵確保每次 JOIN 在該位置最多只會找到一筆參照。使用此查詢作為應用程式的讀取格式，避免另行維護參照值的副本。

node-postgres 應如何插入兩個參照？

在同一個交易中建立套件及其參照。如果任何參照發生衝突，也要回復套件的建立。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 遭拒：該文章已被參照

第二個交易嘗試重複使用文章 101。回復操作會移除套件 3，並讓文章 105 維持未被參照。只有指定名稱的唯一性衝突會顯示重複訊息；其他錯誤則會繼續向上傳遞。PostgreSQL 以 SQLSTATE 23505 標示唯一性違規，並將限制名稱與訊息文字分開提供。PostgreSQL 錯誤代碼

再次執行前述讀取查詢來檢視結果：套件 1 和 2 應會保留，參照文章 101–104。這是每次全新設定後執行一次的示範：再次執行程式時會遇到已存在的套件 2 主鍵。

何時 COALESCE 的唯一索引才足夠？

僅在每列至多包含一個實際參照時使用這個較精簡的設計。COALESCE 會傳回第一個非 null 引數，因此第一欄有值時，第二欄會被索引運算式隱藏。PostgreSQL 也要求其引數能解析為共同型別。條件運算式

例如，資料列 (101, 102) 和 (103, 101) 會產生運算式值 101 和 103。對這些運算式值建立唯一索引會接受兩者，即使文章 101 出現了兩次。這個反例說明了為何運算式需要搭配資料列形狀限制。

若要採用恰好一個欄位有值的規則，先建立前面提到的 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 的布林結果，只有一個欄位為 null、另一個欄位有值時才會接受。PostgreSQL 支援涉及同一資料列多個欄位的檢查。PostgreSQL 14 的限制

唯一運算式索引會將任一欄位中有值的值放入同一比較範圍。運算式索引詳見目前版本的 PostgreSQL和PostgreSQL 14文件；此方法不需要 PostgreSQL 15 新增的 null 處理語法。

第二個 INSERT 不會傳回任何資料列，因為文章 101 已經被參照。運算式可以作為 ON CONFLICT 目標，而 RETURNING 會回報實際插入或更新的資料列。將空結果視為略過插入。運算式索引的名稱不是 ON CONFLICT ON CONSTRAINT 所用的資料表限制名稱。INSERT

如果兩個欄位都可以是空值，請在定義資料表時，將恰好一個欄位有值的檢查改為以下資料列層級條件。保留運算式索引。依照 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 將兩個欄位展開為同一個資料流，再依文章 ID 分組。保留重複項目很重要：一般的 UNION 可能會移除你需要找出的重複證據。以下完整診斷使用合成輸入，可獨立於上述結構描述執行。

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，各出現兩次。若要檢查現有資料表，請將第一個 CTE 換成實際欄位。遷移前請檢視每個回報的項目；自動移除資料列可能會刪除原本預期保留的參照。請將所有參照移至選定的表示方式，並將後續寫入導向該處，因為範例中的不同替代資料表不會共用唯一性強制機制。

為什麼最終決定必須由資料庫強制執行？

事前查詢可以說明現有衝突，但單靠它無法建立唯一性保證。兩個並行操作可能都在任何一方插入之前，觀察到某個參照尚未使用。PostgreSQL 的唯一性實作會在索引插入時檢查衝突，必要時等待互相衝突但尚未提交的交易，並重新檢查結果。索引唯一性檢查

不要用會查詢其他資料列的 CHECK 取代此機制。PostgreSQL 明確指出，不支援以跨資料列檢查作為持續維護的保證。請使用資料列檢查限制允許的欄位形狀，並以唯一限制或索引限制重複參照。限制

JavaScript 程式已通過語法檢查，反例和恰好一個欄位有值的真值表也已確定性檢查。PostgreSQL 範例已對照文件檢視，但未在本研究環境中連線資料庫執行。

對於兩個都有值的參照位置，參照資料表設計可透過單一唯一文章 ID，直接強制此規則。若兩個欄位只能擇一有值，通過檢查的 COALESCE 設計可保留較精簡的結構描述。請根據允許的資料列形狀選擇設計，再由資料庫做出最終的唯一性判定。

來源