Förhindra att ett artikel-ID förekommer mer än en gång i någon av två referenskolumner genom att lägga båda rollerna i en referenstabell med ett unikt artikel-ID. Om varje rad tillåter exakt en ifylld kolumn är ett unikt uttrycksindex ett enklare alternativ.

Den här handledningen riktar sig till en utvecklare som arbetar i macOS Terminal med PostgreSQL och node-postgres. Uppgiften är att upprätthålla en gemensam mängd artikelreferenser: när en artikel väl har refererats i någon av rollerna måste en annan referens till artikeln misslyckas. Alla exempelrader är syntetiska.

Vad innebär unikhet mellan kolumnerna?

Anta att båda kolumnerna refererar till samma artikelkatalog. Den avsedda regeln är att varje icke-null artikel-ID förekommer högst en gång på alla referenspositioner, inklusive de två positionerna inom en och samma rad. Detta är striktare än att göra varje kolumn unik för sig.

Paket Primär artikel Sekundär artikel Resultat enligt den avsedda regeln
1 101 102 Godkänn
2 103 101 Avvisa: 101 förekommer redan i paket 1
3 104 104 Avvisa: samma artikel står på båda positionerna

En sammansatt UNIQUE (primary_article_id, secondary_article_id) gör kombinationen unik. Separata unika begränsningar gör varje kolumn unik oberoende av den andra. Ingen av dessa utformningar avvisar upprepningen mellan kolumnerna i paket 1 och 2. PostgreSQL dokumenterar att ett flerkolumns unikt index avvisar dubbletter när alla indexerade värden matchar. Unika index

Välj utformning utifrån vilka radformer som tillåts innan du skriver INSERT-frågan:

Obligatorisk radform Lämplig utformning
Båda referenserna får vara ifyllda Lagra referenserna som rader; gör article_id unikt
Exakt en referens måste vara ifylld Kräv exakt en med en radkontroll; skapa ett unikt index på COALESCE
Noll eller en referens får vara ifylld Använd en kontroll som tillåter högst en och samma uttrycksindex
Kolumnerna refererar till olika kataloger Fastställ först om lika numeriska ID:n representerar samma sak; den här handledningen utgår från en gemensam katalog

Hur upprätthåller du regeln när båda referenserna är tillåtna?

Representera varje referens som en rad med ett paket, en roll och ett artikel-ID. Rollen bevarar betydelsen hos den ursprungliga kolumnen. En enda unik begränsning på article_id omfattar då båda rollerna.

Schemat nedan kombinerar vanliga begränsningar i PostgreSQL: främmande nycklar kräver matchande katalograder, den sammansatta primärnyckeln tillåter en referens per paket och plats, och den namngivna unika begränsningen tillåter varje artikel bara en gång. Dessa begränsningsmekanismer beskrivs i Begränsningar.

Skapa ett reproducerbart exempel

Använd en tom lokal exempeldatabas med PostgreSQL 14–18 och ett tillgängligt psql-kommando. Spara följande fullständiga konfiguration som schema.sql. Den använder vanliga, opartitionerade tabeller och kräver inget tillägg.

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;

I macOS Terminal anger du namnet på den befintliga exempeldatabasen i PGDATABASE och kör sedan filen. Kommandot förutsätter att dina lokala anslutningsinställningar redan fungerar.

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

Den här utformningen tillåter att ett paket har noll, en eller två referenser. Den upprätthåller unikhet och begränsar antalet roller till två; den kräver inte att varje paket har båda rollerna. Det är ett separat krav. En saknad referens representeras av en saknad rad.

Behåll tvåkolumnsformen vid läsning

Program kan fortfarande hämta en rad per paket med två referensfält. Kör den här frågan efter konfigurationen:

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;

Det första resultatet är paket 1 med referenserna 101 och 102. Schemas primärnyckel säkerställer att varje JOIN hittar högst en referens för sin plats. Använd den här frågan som programmets läsformat i stället för att underhålla en andra kopia av referensvärdena.

Hur infogar node-postgres båda referenserna?

Skapa paketet och dess referenser i en och samma transaktion. Om någon referens leder till en konflikt återställer du även paketet. node-postgres kräver att varje sats i en transaktion använder samma utcheckade klient; separata anrop till pool.query() passar inte för den här sekvensen. Transaktioner

Installera det dokumenterade paketet pg i en lokal projektkatalog. Använd en aktuell LTS-version av Node.js och spara programmet nedan som demo.mjs. Kör det efter SQL-konfigurationen med samma anslutningsmiljö. node-postgres dokumenterar både installation och miljöbaserade anslutningar.

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

Programmet skickar värden via numrerade parametrar och en separat värdearray enligt node-postgres-API:t för frågor. Förväntad utdata vid första körningen:

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

Den andra transaktionen försöker återanvända artikel 101. Återställningen tar bort paket 3 och lämnar artikel 105 utan referenser. Endast konflikten med den specifikt namngivna unikhetsbegränsningen får dubblettmeddelandet; andra fel fortplantas. PostgreSQL identifierar en unikhetsöverträdelse med SQLSTATE 23505 och anger begränsningsnamn separat från meddelandetexten. PostgreSQLs felkoder

Kör den tidigare läsfrågan igen för att granska resultatet: paket 1 och 2 ska finnas kvar med referenserna 101–104. Se det här som en demonstration som körs en gång per nykonfigurerad databas: om du kör programmet igen möter det den befintliga primärnyckeln för paket 2.

När räcker ett unikt index på COALESCE?

Använd den här enklare utformningen endast när en rad innehåller högst en faktisk referens. COALESCE returnerar sitt första argument som inte är null, så en ifylld första kolumn döljer den andra kolumnen från det indexerade uttrycket. PostgreSQL kräver också att argumenten har en gemensam typ. Villkorsuttryck

Till exempel ger raderna (101, 102) och (103, 101) uttrycksvärdena 101 och 103. Ett unikt index på dessa uttrycksvärden godkänner båda trots att artikel 101 förekommer två gånger. Det här moteexemplet visar varför uttrycket behöver en begränsning av radformen.

Följande alternativa exempel visar en regel om exakt en referens. Kör det efter att du skapat den tidigare katalogen articles. Den separata tabellen visar ett alternativt schema i stället för att lägga till referenser i 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;

Kontrollen jämför två booleska resultat som inte är null och godkänner bara när den ena kolumnen är null och den andra är ifylld. PostgreSQL stöder kontroller som omfattar flera kolumner på samma rad. Begränsningar i PostgreSQL 14

Det unika uttrycksindexet placerar det ifyllda värdet från endera kolumnen i samma jämförelsedomän. Uttrycksindex dokumenteras både i aktuell PostgreSQL och i PostgreSQL 14. Metoden kräver inte den ytterligare syntaxen för nullhantering i PostgreSQL 15.

Den andra INSERT-satsen returnerar inga rader eftersom artikel 101 redan har en referens. Ett uttryck kan användas som mål för ON CONFLICT, och RETURNING rapporterar rader som faktiskt infogats eller uppdaterats. Tolka ett tomt resultat som en överhoppad infogning. Uttrycksindexets namn är inte ett tabellbegränsningsnamn som kan användas med ON CONFLICT ON CONSTRAINT. INSERT

Om båda kolumnerna får vara tomma ersätter du kontrollen för exakt en referens med följande villkor på radnivå när du definierar tabellen. Behåll uttrycksindexet. Flera rader där alla värden är null tillåts då enligt PostgreSQLs standardbeteende för nullvärden i unika index. Unika index

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

Hur hittar du dubbletter innan du ändrar en befintlig tabell?

Samla båda kolumnerna i en ström med UNION ALL och gruppera sedan efter artikel-ID. Det är viktigt att behålla dubbletter: vanlig UNION kan ta bort beläggen för den upprepning du behöver hitta. Den här fullständiga diagnosen använder syntetiska indata och körs oberoende av schemana ovan.

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;

Förväntade dubblett-ID:n är 101 och 104, båda med två förekomster. Ersätt den första CTE:n med den befintliga tabellens faktiska kolumner. Granska varje rapporterad förekomst före migreringen; om du tar bort rader automatiskt kan avsedda referenser gå förlorade. Flytta alla referenser till den valda representationen och styr efterföljande skrivningar dit, eftersom de separata alternativtabellerna i exemplet inte delar unikhetskontroll.

Varför måste databasen fatta det slutliga beslutet?

En preliminär sökning kan visa en befintlig konflikt, men den kan inte ensam garantera unikhet. Två samtidiga operationer kan båda se att en referens är oanvänd innan någon av dem infogar den. PostgreSQLs unikhetskontroll görs som en del av indexinfogningen, väntar vid behov på transaktioner som ännu inte har genomförts och kontrollerar sedan resultatet igen. Kontroller av indexunikhet

Ersätt inte den mekanismen med en CHECK som frågar efter andra rader. PostgreSQL stöder uttryckligen inte kontroller mellan rader som en fortlöpande upprätthållen garanti. Använd radkontroller för att begränsa tillåtna kolumnformer och unika begränsningar eller index för upprepade referenser. Begränsningar

JavaScript-programmet klarade en syntaxkontroll, och moteexemplet samt sanningstabellen för exakt en referens kontrollerades deterministiskt. PostgreSQL-exemplen granskades mot dokumentationen men kördes inte mot en databas i den här forskningsmiljön.

När två referenspositioner får vara ifyllda gör referenstabellens utformning regeln direkt upprätthållbar med ett enda unikt artikel-ID. Om kolumnerna måste vara ömsesidigt uteslutande ger den kontrollerade COALESCE-utformningen ett mindre schema. Välj utifrån vilka radformer som är tillåtna och låt sedan databasen fatta det slutliga unikhetsbeslutet.

Källor