Per impedire che un ID articolo compaia più di una volta in una delle due colonne di riferimento, inserisci entrambi i ruoli in una tabella di riferimenti con un unico ID articolo univoco. Se ogni riga consente di valorizzare esattamente una colonna, un indice di espressione univoco è un’opzione più semplice.

Questo tutorial è rivolto a uno sviluppatore che lavora in Terminal su macOS con PostgreSQL e node-postgres. L’obiettivo è imporre un unico insieme condiviso di riferimenti agli articoli: una volta che un articolo è stato referenziato in un ruolo, un altro riferimento a quell’articolo deve essere rifiutato. Tutte le righe di esempio sono sintetiche.

Cosa significa imporre l’unicità tra entrambe le colonne?

Supponiamo che entrambe le colonne facciano riferimento allo stesso catalogo di articoli. La regola desiderata è che ogni ID articolo non nullo compaia al massimo una volta tra tutte le posizioni di riferimento, incluse le due posizioni all’interno di una stessa riga. È un vincolo più forte rispetto a rendere univoca ogni colonna separatamente.

Bundle Articolo primario Articolo secondario Risultato secondo la regola desiderata
1 101 102 Accettato
2 103 101 Rifiutato: 101 è già presente nel bundle 1
3 104 104 Rifiutato: lo stesso articolo occupa entrambe le posizioni

Un UNIQUE (primary_article_id, secondary_article_id) composito rende univoca la combinazione. Vincoli di unicità separati rendono univoca ogni colonna in modo indipendente. Nessuno dei due approcci rifiuta la ripetizione tra colonne nei bundle 1 e 2. PostgreSQL documenta che un indice univoco multicolonna rifiuta i duplicati quando tutti i valori indicizzati coincidono. Indici univoci

Scegli la struttura in base alla forma consentita per le righe prima di scrivere la query di inserimento:

Forma di riga richiesta Struttura adatta
Entrambi i riferimenti possono essere valorizzati Memorizza i riferimenti come righe; rendi univoco article_id
Deve essere valorizzato esattamente un riferimento Impone esattamente un valore con un vincolo di riga; crea un indice univoco su COALESCE
Può essere valorizzato zero o un riferimento Usa un vincolo che consenta al massimo un valore e lo stesso indice di espressione
Le colonne fanno riferimento a cataloghi diversi Prima definisci se ID numerici uguali rappresentano la stessa cosa; questo tutorial presuppone un unico catalogo condiviso

Come si impone la regola quando sono consentiti entrambi i riferimenti?

Rappresenta ogni riferimento come una riga contenente un bundle, un ruolo e un ID articolo. Il ruolo conserva il significato della colonna originale. Un singolo vincolo di unicità su article_id copre quindi entrambi i ruoli.

Lo schema seguente combina i normali vincoli di PostgreSQL: le chiavi esterne richiedono righe corrispondenti nel catalogo, la chiave primaria composita consente un riferimento per bundle e posizione e il vincolo di unicità denominato consente ogni articolo una sola volta. Questi meccanismi sono documentati in Vincoli.

Crea un esempio riproducibile

Usa un database locale di esempio vuoto con PostgreSQL 14–18 e il comando psql disponibile. Salva la configurazione completa seguente come schema.sql. Usa tabelle normali non partizionate e non richiede estensioni.

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 Terminal su macOS, imposta PGDATABASE sul nome di quel database di esempio già esistente, quindi esegui il file. Il comando presuppone che le impostazioni locali della connessione funzionino già.

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

Questa struttura consente a un bundle di avere zero, uno o due riferimenti. Impone l’unicità e un massimo di due ruoli; non richiede che ogni bundle abbia entrambi i ruoli. Questo è un requisito distinto. Un riferimento assente è rappresentato da una riga assente.

Mantieni la struttura a due colonne per la lettura

Le applicazioni possono comunque recuperare una riga per bundle con due campi di riferimento. Esegui questa query dopo la configurazione:

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;

Il risultato iniziale è il bundle 1 con i riferimenti 101 e 102. La chiave primaria dello schema garantisce che ogni join trovi al massimo un riferimento per la propria posizione. Usa questa query come struttura di lettura dell’applicazione, invece di mantenere una seconda copia dei valori di riferimento.

Come dovrebbe inserire entrambi i riferimenti node-postgres?

Crea il bundle e i relativi riferimenti in un’unica transazione. Se un riferimento è in conflitto, esegui il rollback anche del bundle. node-postgres richiede che ogni istruzione di una transazione usi lo stesso client preso dal pool; chiamate separate a pool.query() non sono adatte a questa sequenza. Transazioni

In una directory di progetto locale, installa il pacchetto documentato pg. Usa una versione LTS corrente di Node.js e salva il programma seguente come demo.mjs. Eseguilo dopo la configurazione SQL, usando lo stesso ambiente di connessione. node-postgres documenta sia l’installazione sia le connessioni basate sull’ambiente.

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

Il programma passa i valori tramite parametri numerati e un array di valori separato, seguendo l’API Query di node-postgres. L’output previsto alla prima esecuzione è:

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

La seconda transazione tenta di riutilizzare l’articolo 101. Il rollback rimuove il bundle 3 e lascia l’articolo 105 senza riferimenti. Solo il conflitto di unicità specificamente denominato riceve il messaggio di duplicato; gli altri errori vengono propagati. PostgreSQL identifica una violazione di unicità con SQLSTATE 23505 e fornisce i nomi dei vincoli separatamente dal testo del messaggio. Codici di errore PostgreSQL

Esegui di nuovo la query di lettura precedente per controllare il risultato: dovrebbero rimanere i bundle 1 e 2, con i riferimenti 101–104. Considera questa una dimostrazione da eseguire una sola volta per ogni nuova configurazione: se il programma viene rieseguito, incontra la chiave primaria già esistente del bundle 2.

Quando è sufficiente un indice univoco su COALESCE?

Usa questa struttura più semplice solo quando una riga contiene al massimo un riferimento effettivo. COALESCE restituisce il primo argomento non nullo, quindi una prima colonna valorizzata nasconde la seconda all’espressione indicizzata. PostgreSQL richiede inoltre che gli argomenti abbiano un tipo comune. Espressioni condizionali

Per esempio, le righe (101, 102) e (103, 101) producono valori di espressione 101 e 103. Un indice univoco su tali valori accetta entrambe, anche se l’articolo 101 compare due volte. Questo controesempio spiega perché l’espressione richiede un vincolo sulla forma della riga.

Per una regola che impone esattamente un riferimento, esegui il seguente esempio alternativo dopo aver creato il catalogo precedente articles. La tabella separata mostra uno schema alternativo, invece di aggiungere riferimenti a 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;

Il controllo confronta due risultati booleani non nulli e accetta solo quando una colonna è nulla e l’altra è valorizzata. PostgreSQL supporta controlli che coinvolgono più colonne della stessa riga. Vincoli di PostgreSQL 14

L’indice di espressione univoco inserisce il valore valorizzato di entrambe le colonne in un unico dominio di confronto. Gli indici di espressione sono documentati sia in PostgreSQL corrente sia in PostgreSQL 14; questo approccio non richiede la sintassi aggiuntiva per il trattamento dei valori nulli introdotta in PostgreSQL 15.

Il secondo inserimento non restituisce righe perché l’articolo 101 è già referenziato. Un’espressione può essere usata come destinazione di ON CONFLICT, e RETURNING segnala le righe effettivamente inserite o aggiornate. Considera un risultato vuoto come un inserimento saltato. Il nome dell’indice di espressione non è un nome di vincolo di tabella utilizzabile con ON CONFLICT ON CONSTRAINT. INSERT

Se entrambe le colonne possono essere vuote, sostituisci il controllo che impone esattamente un valore con la seguente condizione a livello di riga durante la definizione della tabella. Mantieni l’indice di espressione. In tal caso, il comportamento predefinito degli indici univoci di PostgreSQL consente più righe con tutti i valori nulli. Indici univoci

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

Come si individuano i duplicati prima di modificare una tabella esistente?

Espandi entrambe le colonne in un unico flusso con UNION ALL, quindi raggruppa per ID articolo. È essenziale mantenere i duplicati: il normale UNION potrebbe rimuovere le prove della ripetizione che devi individuare. Questa diagnostica completa usa dati sintetici ed è indipendente dagli schemi precedenti.

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;

Gli ID duplicati previsti sono 101 e 104, ciascuno con due occorrenze. Per una tabella esistente, sostituisci la prima CTE con le colonne effettive della tabella. Esamina ogni occorrenza segnalata prima della migrazione: rimuovere automaticamente le righe potrebbe eliminare riferimenti intenzionali. Sposta tutti i riferimenti nella struttura scelta e indirizza lì le scritture successive, perché le tabelle alternative separate dell’esempio non condividono il controllo dell’unicità.

Perché il database deve applicare la decisione finale?

Una ricerca preliminare può segnalare un conflitto esistente, ma da sola non può garantire l’unicità. Due operazioni simultanee possono entrambe rilevare che un riferimento non è ancora in uso prima che una delle due lo inserisca. L’implementazione dell’unicità di PostgreSQL controlla i conflitti durante l’inserimento nell’indice, attende se necessario le transazioni non confermate in conflitto e verifica di nuovo il risultato. Controlli di unicità degli indici

Non sostituire questo meccanismo con un CHECK che interroga altre righe. PostgreSQL non supporta esplicitamente i controlli tra righe come garanzia sottoposta a manutenzione continua. Usa controlli a livello di riga per la forma consentita delle colonne e vincoli o indici univoci per impedire la ripetizione dei riferimenti. Vincoli

Il programma JavaScript ha superato un controllo sintattico e il controesempio e la tabella di verità per il vincolo «esattamente uno» sono stati verificati in modo deterministico. Gli esempi PostgreSQL sono stati confrontati con la documentazione, ma non sono stati eseguiti su un database in questo ambiente di ricerca.

Per due posizioni di riferimento valorizzate, la struttura con la tabella dei riferimenti applica direttamente la regola tramite un unico ID articolo univoco. Per una scelta esclusiva tra le colonne, la struttura con COALESCE verificata mantiene uno schema più semplice. Scegli in base alla forma consentita per le righe, poi lascia che sia il database a decidere in via definitiva l’unicità.

Fonti