Una chiave esterna composta in PostgreSQL richiede una chiave univoca idonea che includa le colonne referenziate. Una chiave primaria composta solo da id non è sufficiente per un riferimento a (id, edition), anche se rende logicamente univoca quella coppia. PostgreSQL: CREATE TABLE

Questa guida aiuta uno sviluppatore che lavora in Terminal su un Mac a diagnosticare questa discrepanza, scegliere la relazione appropriata e testare il risultato con node-postgres. Tutte le righe di esempio descrivono articoli sintetici. Le istruzioni SQL sono destinate a PostgreSQL 18 e alle normali chiavi esterne basate sull’uguaglianza; PostgreSQL 18 documenta regole separate per i riferimenti temporali che usano PERIOD. PostgreSQL: CREATE TABLE

Perché PostgreSQL rifiuta il riferimento composto?

Supponiamo che una tabella degli articoli abbia tre colonne: id, edition e title. La sua chiave primaria è id. Un’altra tabella memorizza un riferimento a un articolo sotto forma di due valori: article_id e edition.

La relazione desiderata è espressa da questo frammento di vincolo di tabella:

FOREIGN KEY (article_id, edition)
  REFERENCES demo_articles (id, edition)

Questa dichiarazione chiede a PostgreSQL di verificare insieme i due valori rispetto a una singola riga padre. La destinazione documentata deve essere costituita dalle colonne di un vincolo univoco o di chiave primaria non differibile, oppure dalle colonne di un indice univoco non parziale. La chiave primaria esistente include solo id; il riferimento specifica (id, edition). PostgreSQL: CREATE TABLE

Il ragionamento matematico alla base dell’obiezione è comprensibile: se nessuna riga padre può avere lo stesso id, allora nessuna può avere la stessa coppia (id, edition). Tuttavia, questa implicazione non fornisce la destinazione dichiarata corrispondente richiesta dalla definizione della chiave esterna. In questo schema, aggiungere UNIQUE (id, edition) fornisce tale destinazione. Il vincolo aggiuntivo è logicamente ridondante ai fini dell’univocità, ma svolge una funzione concreta come destinazione del riferimento composto.

Consideriamo queste righe padre a titolo illustrativo:

id edition title
101 morning Paper Shapes
102 evening Color Notes

La coppia figlia (101, 'morning') ha una corrispondenza. La coppia (101, 'evening') non ce l’ha: i suoi due valori compaiono nella tabella, ma in righe diverse. Ecco perché usare una relazione composta quando entrambi i valori memorizzati devono descrivere lo stesso articolo.

Conviene aggiungere la chiave univoca o semplificare la chiave esterna?

Scegli la relazione prima di modificare lo schema. La domanda giusta è se la tabella figlia debba memorizzare e imporre un’edizione insieme all’identificativo dell’articolo.

Comportamento richiesto Schema adatto Conseguenza
Una tabella figlia identifica un articolo e ne mostra l’edizione corrente Memorizzare article_id; fare riferimento a id nella tabella padre Leggere l’edizione dalla tabella padre durante la query
Una tabella figlia memorizza un’edizione che deve corrispondere al proprio articolo Mantenere il riferimento composto; aggiungere UNIQUE (id, edition) alla tabella padre PostgreSQL verifica che la coppia memorizzata esista come tale
Lo stesso numero di articolo può identificare più righe di edizioni Modellare l’identità padre come (id, edition) Una chiave primaria composta solo da id impedirebbe quelle righe padre

L’ultima riga descrive un modello di dati diverso. Non rimuovere la chiave primaria a colonna singola solo per far sparire un errore: prima stabilisci se nella tabella padre debbano davvero comparire numeri di articolo ripetuti. PostgreSQL consente una chiave primaria composta e una tabella può avere una sola chiave primaria. PostgreSQL: Constraints

Nell’esempio eseguibile qui sotto, gli identificativi degli articoli restano univoci a livello globale. La tabella figlia memorizza intenzionalmente anche un’edizione, quindi la tabella padre conserva PRIMARY KEY (id) e aggiunge la coppia univoca.

Come puoi riprodurre e correggere l’errore su un Mac?

Usa un database PostgreSQL 18 locale esistente e un’installazione funzionante di Node.js; questa procedura presuppone che siano già disponibili. Apri Terminal e crea una nuova directory di progetto. Installa il driver con il comando del pacchetto documentato. node-postgres: Welcome

mkdir composite-fk-demo
cd composite-fk-demo
npm init -y
npm install pg

Salva il programma completo seguente come demo.mjs. Crea tabelle temporanee, mostra lo schema rifiutato, aggiunge la chiave corrispondente e testa coppie di articoli valide e non valide. Tutte le istruzioni usano un solo client perché node-postgres richiede che una transazione rimanga sulla stessa istanza client. node-postgres: Transactions

La funzione ausiliaria usa un savepoint per ogni errore previsto, in modo che la transazione possa proseguire. PostgreSQL documenta ROLLBACK TO SAVEPOINT come comando che annulla le istruzioni successive a quel savepoint e consente di riprendere il lavoro. PostgreSQL: ROLLBACK TO SAVEPOINT

import pg from 'pg';
const { Client } = pg;
const client = new Client();

const childDDL = `CREATE TEMP TABLE demo_article_links (
  link_id integer PRIMARY KEY,
  article_id integer NOT NULL,
  edition text NOT NULL,
  FOREIGN KEY (article_id, edition)
    REFERENCES demo_articles (id, edition)
)`;

async function expectError(sql, values, code) {
  await client.query('SAVEPOINT expected_error');
  try {
    await client.query(sql, values);
  } catch (error) {
    await client.query('ROLLBACK TO SAVEPOINT expected_error');
    await client.query('RELEASE SAVEPOINT expected_error');
    if (error.code !== code) throw error;
    console.log(`Expected error: ${code}`);
    return;
  }
  await client.query('ROLLBACK TO SAVEPOINT expected_error');
  await client.query('RELEASE SAVEPOINT expected_error');
  throw new Error(`Expected SQLSTATE ${code}, but query succeeded`);
}

try {
  await client.connect();
  await client.query('BEGIN');
  await client.query(`CREATE TEMP TABLE demo_articles (
    id integer PRIMARY KEY,
    edition text NOT NULL,
    title text NOT NULL
  )`);

  await expectError(childDDL, [], '42830');

  await client.query(`ALTER TABLE demo_articles
    ADD CONSTRAINT demo_articles_id_edition_key
    UNIQUE (id, edition)`);
  await client.query(childDDL);

  await client.query(`INSERT INTO demo_articles (id, edition, title)
    VALUES ($1, $2, $3), ($4, $5, $6)`,
    [101, 'morning', 'Paper Shapes', 102, 'evening', 'Color Notes']);

  await client.query(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [1, 101, 'morning']);

  await expectError(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [2, 101, 'evening'], '23503');

  const result = await client.query(`SELECT l.link_id, a.title, a.edition
    FROM demo_article_links AS l
    JOIN demo_articles AS a
      ON a.id = l.article_id AND a.edition = l.edition
    ORDER BY l.link_id`);
  console.table(result.rows);
} catch (error) {
  console.error(error.code ?? error.message);
  process.exitCode = 1;
} finally {
  try { await client.query('ROLLBACK'); }
  finally { await client.end(); }
}

Il programma passa i valori degli articoli separatamente tramite $1, $2 e i segnaposto successivi, in linea con l’API del driver per le query parametrizzate. I nomi di tabelle e colonne rimangono fissi nell’SQL; i parametri PostgreSQL non possono sostituire gli identificatori. node-postgres: Queries

Esegui il programma con le impostazioni di connessione al tuo database locale esistente. Questo comando presuppone che il database si chiami article_demo, che il server sia in ascolto sulla porta 5432 e che funzionino le impostazioni di connessione locale predefinite già configurate. Modifica il nome del database o la porta, se necessario. node-postgres legge le variabili d’ambiente di connessione a PostgreSQL. node-postgres: Connecting

PGHOST=localhost PGPORT=5432 PGDATABASE=article_demo node demo.mjs

Durante la preparazione di questo articolo, il codice JavaScript ha superato un controllo sintattico. Il comportamento del database è stato verificato sulla base della documentazione; il programma non è stato eseguito qui su un server PostgreSQL.

Cosa dimostrano i due errori e la query finale?

Con i prerequisiti indicati, i messaggi previsti sono:

Expected error: 42830
Expected error: 23503

Il primo errore si verifica durante la creazione della tabella figlia, prima dell’inserimento di qualsiasi riga articolo. PostgreSQL identifica 42830 come invalid_foreign_key. Dopo che la tabella padre ha ricevuto la chiave univoca corrispondente, la stessa definizione della tabella figlia diventa valida. PostgreSQL: Error Codes

Il secondo errore si verifica durante l’inserimento di (101, 'evening'). Lo schema ora è valido, ma la coppia fornita non ha una riga padre corrispondente. PostgreSQL identifica 23503 come foreign_key_violation. Controllare il codice permette di distinguere questa discrepanza tra righe dalla precedente discrepanza tra schemi, senza dipendere dal testo localizzato dell’errore. PostgreSQL: Error Codes

La query finale dovrebbe restituire una riga: collegamento 1, titolo Paper Shapes, edizione morning. La condizione di join include entrambe le componenti della relazione. Così la query descrive la stessa coppia imposta dal vincolo.

Due chiavi esterne separate esprimerebbero una regola diversa. Anche se edition avesse una propria chiave univoca idonea, i riferimenti indipendenti a id e edition permetterebbero a ciascun valore di corrispondere a una riga padre diversa. L’esempio mostra concretamente la differenza: entrambi i singoli valori della coppia rifiutata esistono, ma non esiste la loro combinazione.

L’istruzione finale ROLLBACK elimina intenzionalmente la dimostrazione, comprese le definizioni delle tabelle. Per adattare lo schema a tabelle permanenti, apporta la modifica nello schema mediante la normale procedura di migrazione; copiare questo programma con tabelle temporanee non crea uno schema applicativo duraturo.

Cosa dovresti verificare prima di modificare uno schema esistente?

Parti dall’elenco delle colonne referenziate. Individua la chiave padre che include effettivamente quell’elenco, invece di affidarti al nome di un indice o al fatto che le righe attuali siano casualmente distinte. In questo esempio, l’aggiunta necessaria è il vincolo UNIQUE (id, edition) denominato, mostrato nel programma.

Aggiungere un vincolo univoco crea un indice B-tree univoco. Tieni conto di questo indice aggiuntivo quando scegli tra lo schema composto e la memorizzazione del solo article_id. La dichiarazione della chiave esterna non crea automaticamente un indice sulle colonne figlie. PostgreSQL: Constraints

Prima di aggiungere una chiave esterna a una tabella figlia esistente, verifica che le coppie memorizzate corrispondano a righe padre. Una chiave di destinazione valida risolve il requisito dello schema, ma non corregge i valori non corrispondenti nella tabella figlia. La normale istruzione ALTER TABLE ... ADD CONSTRAINT controlla le righe esistenti. PostgreSQL supporta anche l’aggiunta di una chiave esterna come NOT VALID e la successiva esecuzione di VALIDATE CONSTRAINT; in questo modo il controllo delle righe esistenti viene posticipato, mentre il vincolo viene applicato agli inserimenti o agli aggiornamenti successivi. PostgreSQL: ALTER TABLE

Considera la convalida come una questione distinta dall’idoneità della destinazione. NOT VALID non fornisce la chiave padre mancante. Inoltre, non permette di aggiungere un vincolo univoco senza dimostrare l’unicità: PostgreSQL 18 limita questa opzione ai vincoli di chiave esterna, di controllo e di non nullità. PostgreSQL: ALTER TABLE

Quali condizioni possono cambiare il risultato?

Un indice univoco parziale non è idoneo come destinazione ordinaria della chiave esterna descritta qui. Analogamente, rendere differibile il vincolo univoco referenziato non soddisfa il requisito documentato di un vincolo non differibile. Per questo esempio, usa il valore predefinito semplice UNIQUE (id, edition). PostgreSQL: CREATE TABLE

Anche la gestione dei valori nulli è importante. Con il comportamento di corrispondenza predefinito, una chiave esterna composta non richiede una riga padre corrispondente se uno qualsiasi dei componenti referenzianti è nullo. Nell’esempio entrambi i componenti figli sono dichiarati NOT NULL, così ogni coppia memorizzata deve avere una corrispondenza. Se si desidera una relazione facoltativa, MATCH FULL ammette un riferimento completamente nullo e rifiuta quello parzialmente nullo. PostgreSQL: Constraints

La verifica utile riguarda la coppia in sé: la tabella figlia può indicare un identificativo articolo e un’edizione appartenenti a righe diverse? Se questo deve essere impossibile, mantieni la relazione composta e assegnale una chiave padre corrispondente. Se la tabella figlia deve solo identificare un articolo, fai riferimento a id e recupera l’edizione di quell’articolo durante la query.

Fonti