To prevent an article ID from appearing in either of two reference columns more than once, put both roles into a reference table with one unique article ID. If each row permits exactly one populated column, a unique expression index is a simpler option.

This tutorial is for a developer working in macOS Terminal with PostgreSQL and node-postgres. The task is to enforce one shared pool of article references: once an article is referenced in either role, another reference to that article must fail. All sample rows are synthetic.

What does uniqueness across either column mean?

Assume both columns reference the same article catalog. The intended rule is that every non-null article ID occurs at most once across all reference positions, including the two positions within one row. This is stronger than making each column unique separately.

Bundle Primary article Secondary article Result under the intended rule
1 101 102 Accept
2 103 101 Reject: 101 already appears in bundle 1
3 104 104 Reject: the same article occupies both positions

A composite UNIQUE (primary_article_id, secondary_article_id) makes the combination unique. Separate unique constraints make each column independently unique. Neither design rejects the cross-column repetition between bundles 1 and 2. PostgreSQL documents that a multicolumn unique index rejects duplicates when all indexed values match. Unique Indexes

Choose the design from the permitted row shape before writing the insert query:

Required row shape Suitable design
Both references may be populated Store references as rows; make article_id unique
Exactly one reference must be populated Enforce exactly one with a row check; uniquely index COALESCE
Zero or one reference may be populated Use an at-most-one check and the same expression index
Columns reference different catalogs First define whether equal numeric IDs represent the same thing; this tutorial assumes one shared catalog

How do you enforce the rule when both references are allowed?

Represent each reference as a row containing a bundle, a role and an article ID. The role preserves the meaning of the original column. A single unique constraint on article_id then covers both roles.

The schema below combines ordinary PostgreSQL constraints: foreign keys require matching catalog rows, the composite primary key permits one reference per bundle and slot, and the named unique constraint permits each article only once. These constraint mechanisms are documented in Constraints.

Create a reproducible sample

Use an empty local example database with PostgreSQL 14–18 and an available psql command. Save the following complete setup as schema.sql. It uses ordinary, unpartitioned tables and requires no extension.

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;

In macOS Terminal, set PGDATABASE to the name of that existing example database, then run the file. The command assumes your local connection settings already work.

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

This design permits a bundle to have zero, one or two references. It enforces uniqueness and the maximum of two roles; it does not require every bundle to have both roles. That is a separate requirement. An absent reference is represented by an absent row.

Keep the two-column shape for reading

Applications can still retrieve one row per bundle with two reference fields. Run this query after the setup:

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;

The initial result is bundle 1 with references 101 and 102. The schema’s primary key ensures each join finds at most one reference for its slot. Use this query as the application’s read shape rather than maintaining a second copy of the reference values.

How should node-postgres insert both references?

Create the bundle and its references in one transaction. If any reference conflicts, roll back the bundle as well. node-postgres requires every statement in a transaction to use the same checked-out client; separate calls to pool.query() are unsuitable for this sequence. Transactions

In a local project directory, install the documented pg package. Use a current Node.js LTS release and save the program below as demo.mjs. Run it after the SQL setup, with the same connection environment. node-postgres documents both installation and environment-based connections.

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();
}

The program passes values through numbered parameters and a separate values array, following the node-postgres Queries API. The expected first-run output is:

Added bundle 2
Bundle 3 rejected: an article is already referenced

The second transaction attempts to reuse article 101. Its rollback removes bundle 3 and leaves article 105 unreferenced. Only the specifically named uniqueness conflict receives the duplicate message; other failures propagate. PostgreSQL identifies a uniqueness violation with SQLSTATE 23505 and supplies constraint names separately from message text. PostgreSQL Error Codes

Run the earlier read query again to inspect the result: bundles 1 and 2 should remain, with references 101–104. Treat this as a once-per-fresh-setup demonstration: rerunning the program encounters the existing bundle 2 primary key.

When is a unique index on COALESCE sufficient?

Use this smaller design only when a row contains at most one actual reference. COALESCE returns its first non-null argument, so a populated first column hides the second column from the indexed expression. PostgreSQL also requires its arguments to resolve to a common type. Conditional Expressions

For example, rows (101, 102) and (103, 101) produce expression values 101 and 103. A unique index on those expression values accepts both, even though article 101 appears twice. This counterexample explains why the expression needs a row-shape constraint.

For an exactly-one rule, run the following alternative example after creating the earlier articles catalog. Its separate table demonstrates an alternative schema, rather than adding references to 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;

The check compares two non-null Boolean results and accepts only when one column is null and the other is populated. PostgreSQL supports checks involving multiple columns in the same row. PostgreSQL 14 Constraints

The unique expression index puts either column’s populated value into one comparison domain. Expression indexes are documented in both current PostgreSQL and PostgreSQL 14; this approach does not require PostgreSQL 15’s additional null-treatment syntax.

The second insert returns no rows because article 101 is already referenced. An expression can be an ON CONFLICT target, and RETURNING reports rows actually inserted or updated. Treat an empty result as a skipped insert. The expression index’s name is not a table constraint name for ON CONFLICT ON CONSTRAINT. INSERT

If both columns may be empty, replace the exactly-one check with the following row-level condition when defining the table. Keep the expression index. Multiple all-null rows are then allowed under PostgreSQL’s default unique-index null behavior. Unique Indexes

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

How do you diagnose duplicates before changing an existing table?

Expand both columns into one stream with UNION ALL, then group by article ID. Keeping duplicates is essential: ordinary UNION could remove evidence of the repetition you need to find. This complete diagnostic uses synthetic input and runs independently of the schemas above.

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;

The expected duplicate IDs are 101 and 104, each with two occurrences. For an existing table, replace the first CTE with the table’s actual columns. Review every reported occurrence before migration; removing rows automatically could discard intended references. Move all references into the chosen representation and direct subsequent writes there, because the sample’s separate alternative tables do not share uniqueness enforcement.

Why must the database enforce the final decision?

A preliminary lookup can explain an existing conflict, but it cannot establish the uniqueness guarantee by itself. Two concurrent operations can both observe an unused reference before either inserts it. PostgreSQL’s uniqueness implementation checks conflicts as part of index insertion, waits for conflicting uncommitted transactions when necessary, and rechecks the outcome. Index Uniqueness Checks

Do not replace that mechanism with a CHECK that queries other rows. PostgreSQL explicitly does not support cross-row checks as a continuously maintained guarantee. Use row checks for the permitted column shape and unique constraints or indexes for repeated references. Constraints

The JavaScript program passed a syntax check, and the counterexample and exactly-one truth table were checked deterministically. The PostgreSQL examples were inspected against the documentation but were not executed against a database in this research environment.

For two populated reference positions, the reference-table design makes the rule directly enforceable through one unique article ID. For an exclusive choice between columns, the checked COALESCE design keeps the smaller schema. Choose from the permitted row shape, then let the database make the final uniqueness decision.

Sources