Voorkom dat een artikel-ID meer dan één keer voorkomt in een van de twee referentiekolommen door beide rollen in een referentietabel met één unieke artikel-ID op te slaan. Als elke rij precies één ingevulde kolom mag hebben, is een unieke expressie-index een eenvoudigere optie.

Deze tutorial is bedoeld voor een ontwikkelaar die op macOS met Terminal, PostgreSQL en node-postgres werkt. De taak is om één gedeelde verzameling artikelverwijzingen af te dwingen: zodra naar een artikel in een van beide rollen is verwezen, moet een volgende verwijzing naar dat artikel mislukken. Alle voorbeeldrijen zijn synthetisch.

Wat betekent uniciteit over beide kolommen?

Stel dat beide kolommen naar dezelfde artikelcatalogus verwijzen. De bedoelde regel is dat elke niet-null artikel-ID hoogstens één keer voorkomt op alle referentieposities, inclusief de twee posities binnen één rij. Dit is strenger dan elke kolom afzonderlijk uniek maken.

Bundel Primair artikel Secundair artikel Resultaat volgens de bedoelde regel
1 101 102 Accepteren
2 103 101 Afwijzen: 101 komt al voor in bundel 1
3 104 104 Afwijzen: hetzelfde artikel staat op beide posities

Een samengestelde UNIQUE (primary_article_id, secondary_article_id) maakt de combinatie uniek. Afzonderlijke unieke beperkingen maken elke kolom onafhankelijk uniek. Geen van beide ontwerpen wijst de herhaling over kolommen heen tussen bundels 1 en 2 af. PostgreSQL documenteert dat een unieke index met meerdere kolommen duplicaten afwijst wanneer alle geïndexeerde waarden overeenkomen. Unieke indexen

Kies het ontwerp op basis van de toegestane rijvorm voordat je de insertquery schrijft:

Vereiste rijvorm Geschikt ontwerp
Beide verwijzingen mogen ingevuld zijn Sla verwijzingen op als rijen; maak article_id uniek
Precies één verwijzing moet ingevuld zijn Dwing precies één af met een rijcontrole; indexeer COALESCE uniek
Nul of één verwijzing mag ingevuld zijn Gebruik een controle voor maximaal één en dezelfde expressie-index
Kolommen verwijzen naar verschillende catalogi Bepaal eerst of gelijke numerieke ID's hetzelfde voorstellen; deze tutorial gaat uit van één gedeelde catalogus

Hoe dwing je de regel af als beide verwijzingen zijn toegestaan?

Stel elke verwijzing voor als een rij met een bundel, een rol en een artikel-ID. De rol behoudt de betekenis van de oorspronkelijke kolom. Eén unieke beperking op article_id omvat dan beide rollen.

Het onderstaande schema combineert gewone PostgreSQL-beperkingen: foreign keys vereisen overeenkomstige catalogusrijen, de samengestelde primary key staat één verwijzing per bundel en positie toe, en de benoemde unieke beperking staat elk artikel maar één keer toe. Deze beperkingsmechanismen zijn gedocumenteerd in Beperkingen.

Een reproduceerbaar voorbeeld maken

Gebruik een lege lokale voorbeelddatabase met PostgreSQL 14–18 en een beschikbaar psql-commando. Sla de volgende volledige configuratie op als schema.sql. Deze gebruikt gewone, niet-gepartitioneerde tabellen en vereist geen extensie.

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;

Stel in macOS Terminal PGDATABASE in op de naam van die bestaande voorbeelddatabase en voer het bestand uit. Het commando gaat ervan uit dat je lokale verbindingsinstellingen al werken.

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

Met dit ontwerp kan een bundel nul, één of twee verwijzingen hebben. Het dwingt uniciteit en een maximum van twee rollen af; het vereist niet dat elke bundel beide rollen heeft. Dat is een afzonderlijke vereiste. Een ontbrekende verwijzing wordt weergegeven door een ontbrekende rij.

De tweekolomsvorm behouden voor lezen

Applicaties kunnen nog steeds één rij per bundel met twee referentievelden ophalen. Voer deze query uit na de configuratie:

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;

Het eerste resultaat is bundel 1 met verwijzingen 101 en 102. De primary key van het schema zorgt ervoor dat elke join hoogstens één verwijzing voor zijn positie oplevert. Gebruik deze query als leesvorm voor de applicatie in plaats van een tweede kopie van de referentiewaarden bij te houden.

Hoe voeg je beide verwijzingen in met node-postgres?

Maak de bundel en de bijbehorende verwijzingen aan binnen één transactie. Als een verwijzing conflicteert, draai dan ook het aanmaken van de bundel terug. node-postgres vereist dat elke instructie in een transactie dezelfde gereserveerde client gebruikt; afzonderlijke aanroepen van pool.query() zijn hiervoor ongeschikt. Transacties

Installeer in een lokale projectmap het gedocumenteerde pakket pg. Gebruik een actuele Node.js LTS-release en sla het onderstaande programma op als demo.mjs. Voer het uit na de SQL-configuratie, met dezelfde verbindingsomgeving. node-postgres documenteert zowel de installatie als verbindingen op basis van omgevingsvariabelen.

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

Het programma geeft waarden door via genummerde parameters en een afzonderlijke waardenarray, volgens de node-postgres-API voor Queries. De verwachte uitvoer bij de eerste uitvoering is:

Bundel 2 toegevoegd
Bundel 3 afgewezen: er wordt al naar een artikel verwezen

De tweede transactie probeert artikel 101 opnieuw te gebruiken. De rollback verwijdert bundel 3 en laat artikel 105 onverwezen. Alleen het specifiek benoemde uniciteitsconflict krijgt het duplicaatbericht; andere fouten worden doorgegeven. PostgreSQL duidt een uniciteitsschending aan met SQLSTATE 23505 en levert beperkingsnamen afzonderlijk van de berichttekst aan. PostgreSQL-foutcodes

Voer de eerdere leesquery opnieuw uit om het resultaat te bekijken: bundels 1 en 2 moeten behouden blijven, met verwijzingen 101–104. Beschouw dit als een demonstratie die je één keer per nieuwe configuratie uitvoert: bij opnieuw uitvoeren stuit het programma op de bestaande primary key van bundel 2.

Wanneer volstaat een unieke index op COALESCE?

Gebruik dit kleinere ontwerp alleen wanneer een rij hoogstens één daadwerkelijke verwijzing bevat. COALESCE retourneert het eerste niet-null argument, zodat een ingevulde eerste kolom de tweede kolom voor de geïndexeerde expressie verbergt. PostgreSQL vereist bovendien dat de argumenten naar een gemeenschappelijk type kunnen worden herleid. Voorwaardelijke expressies

De rijen (101, 102) en (103, 101) leveren bijvoorbeeld expressiewaarden 101 en 103 op. Een unieke index op die expressiewaarden accepteert beide, ook al komt artikel 101 tweemaal voor. Dit tegenvoorbeeld laat zien waarom de rijvorm door een beperking moet worden afgedwongen.

Voer voor een regel die precies één verwijzing vereist het volgende alternatieve voorbeeld uit nadat je de eerdere catalogus articles hebt aangemaakt. De afzonderlijke tabel illustreert een alternatief schema, in plaats van verwijzingen toe te voegen aan 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;

De controle vergelijkt twee niet-null Booleaanse resultaten en accepteert alleen wanneer de ene kolom null is en de andere is ingevuld. PostgreSQL ondersteunt controles die meerdere kolommen binnen dezelfde rij omvatten. Beperkingen in PostgreSQL 14

De unieke expressie-index plaatst de ingevulde waarde van een van beide kolommen in één vergelijkingsdomein. Expressie-indexen zijn gedocumenteerd in zowel de huidige PostgreSQL-documentatie als PostgreSQL 14; voor deze aanpak is de aanvullende null-behandelingssyntaxis van PostgreSQL 15 niet nodig.

De tweede insert retourneert geen rijen omdat naar artikel 101 al wordt verwezen. Een expressie kan als ON CONFLICT-doel dienen, en RETURNING rapporteert rijen die daadwerkelijk zijn ingevoegd of bijgewerkt. Beschouw een leeg resultaat als een overgeslagen insert. De naam van de expressie-index is geen tabelbeperkingsnaam voor ON CONFLICT ON CONSTRAINT. INSERT

Als beide kolommen leeg mogen zijn, vervang dan de controle voor precies één door de volgende voorwaarde op rijniveau bij het definiëren van de tabel. Behoud de expressie-index. Meerdere rijen waarin beide kolommen null zijn, zijn dan toegestaan volgens het standaardgedrag van PostgreSQL voor null-waarden in unieke indexen. Unieke indexen

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

Hoe spoor je duplicaten op voordat je een bestaande tabel wijzigt?

Vouw beide kolommen met UNION ALL uit tot één stroom en groepeer vervolgens op artikel-ID. Duplicaten behouden is essentieel: gewone UNION kan het bewijs verwijderen van de herhaling die je probeert te vinden. Deze volledige diagnose gebruikt synthetische invoer en draait onafhankelijk van de bovenstaande schema's.

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;

De verwachte dubbele ID's zijn 101 en 104, elk met twee voorkomens. Vervang voor een bestaande tabel de eerste CTE door de daadwerkelijke kolommen van de tabel. Controleer elk gerapporteerd voorkomen vóór de migratie; automatisch verwijderen van rijen kan bedoelde verwijzingen laten verdwijnen. Verplaats alle verwijzingen naar de gekozen representatie en stuur toekomstige schrijfbewerkingen daarheen, omdat de afzonderlijke alternatieve tabellen van het voorbeeld de uniciteitsafdwinging niet delen.

Waarom moet de database de uiteindelijke beslissing afdwingen?

Een voorafgaande controle kan een bestaand conflict verklaren, maar kan de uniciteitsgarantie niet zelfstandig vastleggen. Twee gelijktijdige bewerkingen kunnen allebei vaststellen dat een verwijzing nog niet wordt gebruikt voordat een van beide die invoegt. De uniciteitsimplementatie van PostgreSQL controleert conflicten tijdens het invoegen in de index, wacht indien nodig op conflicterende niet-vastgelegde transacties en controleert de uitkomst opnieuw. Controles op indexuniciteit

Vervang dit mechanisme niet door een CHECK die andere rijen bevraagt. PostgreSQL ondersteunt controles over meerdere rijen expliciet niet als een continu afgedwongen garantie. Gebruik rijcontroles voor de toegestane kolomvorm en unieke beperkingen of indexen voor verwijzingen die anders dubbel voorkomen. Beperkingen

Het JavaScript-programma slaagde voor een syntaxiscontrole en het tegenvoorbeeld en de waarheidstabel voor precies één verwijzing zijn deterministisch gecontroleerd. De PostgreSQL-voorbeelden zijn getoetst aan de documentatie, maar niet uitgevoerd op een database in deze onderzoeksomgeving.

Voor twee ingevulde referentieposities maakt het ontwerp met een referentietabel de regel rechtstreeks afdwingbaar met één unieke artikel-ID. Als je tussen kolommen precies één keuze toestaat, biedt het gecontroleerde ontwerp met COALESCE een kleiner schema. Kies op basis van de toegestane rijvorm en laat de database vervolgens de uiteindelijke beslissing over uniciteit nemen.

Bronnen