Um zu verhindern, dass eine Artikel-ID in einer der beiden Referenzspalten mehr als einmal vorkommt, fassen Sie beide Rollen in einer Referenztabelle mit einer eindeutigen Artikel-ID zusammen. Wenn jede Zeile genau eine befüllte Spalte zulässt, ist ein eindeutiger Ausdrucksindex die einfachere Option.

Dieses Tutorial richtet sich an Entwickler, die unter macOS im Terminal mit PostgreSQL und node-postgres arbeiten. Die Aufgabe besteht darin, einen gemeinsamen Pool von Artikelreferenzen durchzusetzen: Sobald ein Artikel in einer der beiden Rollen referenziert wird, muss ein weiterer Verweis auf denselben Artikel fehlschlagen. Alle Beispielzeilen sind synthetisch.

Was bedeutet Eindeutigkeit über eine der beiden Spalten?

Angenommen, beide Spalten referenzieren denselben Artikelkatalog. Die beabsichtigte Regel besagt, dass jede nicht-null Artikel-ID höchstens einmal über alle Referenzpositionen hinweg vorkommt, einschließlich der beiden Positionen innerhalb einer Zeile. Dies ist stärker, als jede Spalte einzeln eindeutig zu machen.

Bundle Primärer Artikel Sekundärer Artikel Ergebnis unter der beabsichtigten Regel
1 101 102 Akzeptieren
2 103 101 Ablehnen: 101 kommt bereits in Bundle 1 vor
3 104 104 Ablehnen: derselbe Artikel belegt beide Positionen

Ein zusammengesetzter UNIQUE (primary_article_id, secondary_article_id) macht die Kombination eindeutig. Getrennte Unique-Constraints machen jede Spalte unabhängig voneinander eindeutig. Keines der beiden Designs weist die spaltenübergreifende Wiederholung zwischen Bundle 1 und 2 zurück. PostgreSQL dokumentiert, dass ein mehrspaltiger Unique-Index Duplikate nur zurückweist, wenn alle indizierten Werte übereinstimmen. Unique Indexes

Wählen Sie das Design basierend auf der zulässigen Zeilenform, bevor Sie die Insert-Abfrage schreiben:

Erforderliche Zeilenform Geeignetes Design
Beide Referenzen dürfen befüllt sein Referenzen als Zeilen speichern; article_id eindeutig machen
Genau eine Referenz muss befüllt sein Genau eine mit einer Zeilen-Prüfung erzwingen; COALESCE eindeutig indizieren
Null oder eine Referenz darf befüllt sein Eine „höchstens eins“-Prüfung und denselben Ausdrucksindex verwenden
Spalten referenzieren verschiedene Kataloge Zuerst definieren, ob gleiche numerische IDs dasselbe bedeuten; dieses Tutorial geht von einem gemeinsamen Katalog aus

Wie setzen Sie die Regel durch, wenn beide Referenzen erlaubt sind?

Stellen Sie jede Referenz als Zeile dar, die ein Bundle, eine Rolle und eine Artikel-ID enthält. Die Rolle bewahrt die Bedeutung der ursprünglichen Spalte. Ein einziger Unique-Constraint auf article_id deckt dann beide Rollen ab.

Das folgende Schema kombiniert gewöhnliche PostgreSQL-Constraints: Fremdschlüssel fordern passende Katalogzeilen, der zusammengesetzte Primärschlüssel erlaubt eine Referenz pro Bundle und Slot, und der benannte Unique-Constraint erlaubt jeden Artikel nur einmal. Diese Constraint-Mechanismen sind in Constraints dokumentiert.

Ein reproduzierbares Beispiel erstellen

Verwenden Sie eine leere lokale Beispieldatenbank mit PostgreSQL 14–18 und einem verfügbaren psql-Befehl. Speichern Sie das folgende vollständige Setup als schema.sql. Es verwendet gewöhnliche, nicht partitionierte Tabellen und erfordert keine 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;

Im macOS Terminal setzen Sie PGDATABASE auf den Namen jener bestehenden Beispieldatenbank und führen die Datei aus. Der Befehl geht davon aus, dass Ihre lokalen Verbindungseinstellungen bereits funktionieren.

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

Dieses Design erlaubt einem Bundle, null, eine oder zwei Referenzen zu haben. Es erzwingt Eindeutigkeit und das Maximum von zwei Rollen; es verlangt nicht, dass jedes Bundle beide Rollen hat. Das ist eine separate Anforderung. Eine fehlende Referenz wird durch eine fehlende Zeile dargestellt.

Die Zweispalten-Form für das Lesen beibehalten

Anwendungen können weiterhin eine Zeile pro Bundle mit zwei Referenzfeldern abrufen. Führen Sie diese Abfrage nach dem Setup aus:

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;

Das anfängliche Ergebnis ist Bundle 1 mit den Referenzen 101 und 102. Der Primärschlüssel des Schemas stellt sicher, dass jeder Join höchstens eine Referenz für seinen Slot findet. Verwenden Sie diese Abfrage als Leseform der Anwendung, anstatt eine zweite Kopie der Referenzwerte zu pflegen.

Wie sollte node-postgres beide Referenzen einfügen?

Erstellen Sie das Bundle und seine Referenzen in einer Transaktion. Wenn eine Referenz einen Konflikt verursacht, rollen Sie das Bundle ebenfalls zurück. node-postgres verlangt, dass jede Anweisung in einer Transaktion denselben ausgecheckten Client verwendet; separate Aufrufe von pool.query() eignen sich nicht für diese Sequenz. Transactions

In einem lokalen Projektverzeichnis installieren Sie das dokumentierte pg-Paket. Verwenden Sie ein aktuelles Node.js LTS-Release und speichern Sie das folgende Programm als demo.mjs. Führen Sie es nach dem SQL-Setup mit derselben Verbindungsumgebung aus. node-postgres dokumentiert sowohl Installation als auch umgebungsbasierte Verbindungen.

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

Das Programm übergibt Werte über nummerierte Parameter und ein separates Werte-Array, gemäß der node-postgres Queries API. Die erwartete Ausgabe beim ersten Lauf ist:

Bundle 2 hinzugefügt
Bundle 3 abgelehnt: ein Artikel ist bereits referenziert

Die zweite Transaktion versucht, Artikel 101 erneut zu verwenden. Ihr Rollback entfernt Bundle 3 und lässt Artikel 105 unreferenziert. Nur der spezifisch benannte Eindeutigkeitskonflikt erhält die Duplikatmeldung; andere Fehler werden weitergeleitet. PostgreSQL identifiziert einen Eindeutigkeitsverstoß mit SQLSTATE 23505 und liefert Constraint-Namen getrennt vom Nachrichtentext. PostgreSQL Error Codes

Führen Sie die vorherige Leseabfrage erneut aus, um das Ergebnis zu prüfen: Bundle 1 und 2 sollten verbleiben, mit Referenzen 101–104. Behandeln Sie dies als einmalige Demonstration pro frischem Setup: Ein erneutes Ausführen des Programms stößt auf den bestehenden Primärschlüssel von Bundle 2.

Wann genügt ein Unique-Index auf COALESCE?

Verwenden Sie dieses kleinere Design nur, wenn eine Zeile höchstens eine tatsächliche Referenz enthält. COALESCE gibt das erste nicht-null-Argument zurück, sodass eine befüllte erste Spalte die zweite Spalte vor dem indizierten Ausdruck verbirgt. PostgreSQL verlangt auch, dass sich seine Argumente auf einen gemeinsamen Typ auflösen. Conditional Expressions

Zum Beispiel erzeugen die Zeilen (101, 102) und (103, 101) die Ausdruckswerte 101 und 103. Ein Unique-Index auf diesen Ausdruckswerten akzeptiert beide, obwohl Artikel 101 zweimal vorkommt. Dieses Gegenbeispiel erklärt, warum der Ausdruck eine Zeilenform-Prüfung benötigt.

Für eine „genau-eins“-Regel führen Sie das folgende alternative Beispiel nach dem Erstellen des früheren articles-Katalogs aus. Seine separate Tabelle demonstriert ein alternatives Schema, anstatt Referenzen zu bundle_refs hinzuzufügen.

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;

Der Check vergleicht zwei nicht-null-Boolesche Ergebnisse und akzeptiert nur, wenn eine Spalte null und die andere befüllt ist. PostgreSQL unterstützt Prüfungen, die mehrere Spalten in derselben Zeile betreffen. PostgreSQL 14 Constraints

Der eindeutige Ausdrucksindex bringt den befüllten Wert jeder Spalte in eine Vergleichsdomäne. Expression Indexes sind sowohl in der aktuellen PostgreSQL als auch in der PostgreSQL 14 Dokumentation beschrieben; dieser Ansatz erfordert nicht die zusätzliche Null-Behandlungssyntax von PostgreSQL 15.

Der zweite Insert gibt keine Zeilen zurück, weil Artikel 101 bereits referenziert ist. Ein Ausdruck kann ein ON CONFLICT-Ziel sein, und RETURNING meldet tatsächlich eingefügte oder aktualisierte Zeilen. Behandeln Sie ein leeres Ergebnis als übersprungenen Insert. Der Name des Ausdrucksindex ist kein Tabellenconstraint-Name für ON CONFLICT ON CONSTRAINT. INSERT

Wenn beide Spalten leer sein dürfen, ersetzen Sie den „genau-eins“-Check durch die folgende Zeilenbedingung bei der Tabellendefinition. Behalten Sie den Ausdrucksindex bei. Mehrere all-null-Zeilen sind dann unter PostgreSQLs Standardverhalten bei Unique-Indizes erlaubt. Unique Indexes

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

Wie diagnostizieren Sie Duplikate, bevor Sie eine bestehende Tabelle ändern?

Erweitern Sie beide Spalten mit UNION ALL zu einem Stream und gruppieren Sie nach Artikel-ID. Das Beibehalten von Duplikaten ist essenziell: ein gewöhnliches UNION könnte die Beweise für die Wiederholung entfernen, die Sie finden müssen. Diese vollständige Diagnose verwendet synthetische Eingaben und läuft unabhängig von den obigen Schemata.

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;

Die erwarteten doppelten IDs sind 101 und 104, jeweils mit zwei Vorkommen. Für eine bestehende Tabelle ersetzen Sie das erste CTE durch die tatsächlichen Spalten der Tabelle. Prüfen Sie jeden gemeldeten Fall vor der Migration; automatisches Entfernen von Zeilen könnte beabsichtigte Referenzen verwerfen. Verschieben Sie alle Referenzen in die gewählte Repräsentation und leiten Sie nachfolgende Schreibzugriffe dorthin, weil die separaten Alternativtabellen des Beispiels keine Eindeutigkeitserzwingung teilen.

Warum muss die Datenbank die endgültige Entscheidung durchsetzen?

Eine vorläufige Abfrage kann einen bestehenden Konflikt erklären, aber sie kann die Eindeutigkeitsgarantie nicht selbst herstellen. Zwei gleichzeitige Operationen können beide eine unbenutzte Referenz beobachten, bevor eine von ihnen sie einfügt. PostgreSQLs Eindeutigkeitsimplementierung prüft Konflikte als Teil der Indexeinfügung, wartet bei Bedarf auf konkurrierende uncommittete Transaktionen und prüft das Ergebnis erneut. Index Uniqueness Checks

Ersetzen Sie diesen Mechanismus nicht durch einen CHECK, der andere Zeilen abfragt. PostgreSQL unterstützt explizit keine zeilenübergreifenden Prüfungen als kontinuierlich gewahrte Garantie. Verwenden Sie Zeilenprüfungen für die zulässige Spaltenform und Unique-Constraints oder -Indizes für wiederholte Referenzen. Constraints

Das JavaScript-Programm bestand einen Syntax-Check, und das Gegenbeispiel sowie die Wahrheitswerttabelle für „genau eins“ wurden deterministisch geprüft. Die PostgreSQL-Beispiele wurden anhand der Dokumentation inspiziert, aber nicht in dieser Forschungsumgebung gegen eine Datenbank ausgeführt.

Für zwei befüllte Referenzpositionen macht das Referenztabellen-Design die Regel direkt durch einen eindeutigen Artikel-ID erzwingbar. Für eine exklusive Wahl zwischen Spalten behält das geprüfte COALESCE-Design das kleinere Schema bei. Wählen Sie basierend auf der zulässigen Zeilenform und lassen Sie die Datenbank die endgültige Eindeutigkeitsentscheidung treffen.

Quellen