Una chiave esterna PostgreSQL NOT VALID salta il controllo iniziale delle righe esistenti, applicando comunque i nuovi riferimenti. Le righe precedenti con riferimenti non corrispondenti restano finché non vengono corrette e validate. PostgreSQL 17: ALTER TABLE

Questa guida è rivolta a uno sviluppatore che lavora nel Terminale su un Mac e vuole aggiungere una chiave esterna a tabelle popolate e comprenderne i risultati. L’esempio completo con node-postgres usa solo dati sintetici relativi ad articoli e segue un riferimento non corrispondente dalla creazione del vincolo alla correzione e alla validazione riuscita.

Cosa succede alle righe esistenti e alle nuove scritture?

Consideriamo due tabelle: articles contiene gli ID e i titoli degli articoli, mentre article_links contiene riferimenti a tali ID. Un collegamento non corrisponde a un articolo quando il suo article_id, se non nullo, non ha un articolo corrispondente. Aggiungere la chiave esterna con NOT VALID consente di mantenere quella discrepanza precedente e, al contempo, di applicare il vincolo alle scritture successive. PostgreSQL 17: ALTER TABLE

Operazione dopo l’aggiunta del vincolo Risultato in questo esempio
Lasciare invariato un collegamento precedente senza corrispondenza Rimane memorizzato
Inserire un collegamento che fa riferimento a un articolo esistente Riesce
Inserire un collegamento che fa riferimento a un articolo inesistente Fallisce
Modificare l’ID dell’articolo di un collegamento impostandolo su un altro ID inesistente Fallisce
Validare mentre resta un collegamento senza corrispondenza La validazione fallisce
Correggere tutti i riferimenti senza corrispondenza e poi validare La validazione può riuscire

Questi risultati riflettono la distinzione documentata tra aggiunta e validazione del vincolo. PostgreSQL 17: ALTER TABLE

Un’eccezione importante riguarda gli aggiornamenti che lasciano invariato il valore della chiave esterna. In PostgreSQL 18, l’implementazione può saltare il controllo della chiave esterna quando le chiavi vecchia e nuova risultano uguali. Di conseguenza, la modifica della sola etichetta di un collegamento precedente senza corrispondenza può riuscire senza correggerne il riferimento. L’implementazione include anche un caso speciale per una riga inserita dalla transazione corrente, quindi questo comportamento non va generalizzato a ogni sequenza di aggiornamenti. Implementazione delle chiavi esterne di PostgreSQL 18

La conseguenza pratica è che gli aggiornamenti delle etichette riusciti sono un indicatore inadeguato dell’avvenuta pulizia dei dati. Controlla direttamente i riferimenti senza corrispondenza e usa la validazione per accertare che il vincolo sia rispettato nell’intera tabella.

Quale versione di PostgreSQL e quale struttura delle tabelle sono necessarie?

L’esempio seguente usa tabelle ordinarie, non partizionate, ed è pensato per PostgreSQL 17 e 18. Le tabelle partizionate presentano una differenza significativa tra le versioni: PostgreSQL 17 vieta esplicitamente di dichiarare chiavi esterne NOT VALID sulle tabelle partizionate; PostgreSQL 18 ha introdotto questa possibilità. Controlla la versione del server anziché dedurre il supporto dalla versione del pacchetto Node installato. PostgreSQL 17: ALTER TABLE, Note di rilascio di PostgreSQL 18

Per una tabella referenziante partizionata su PostgreSQL 17, il percorso diretto supportato consiste nel correggere i riferimenti e aggiungere normalmente la chiave esterna, consentendo la scansione iniziale di validazione. Se la validazione per fasi sulla tabella partizionata principale è essenziale, PostgreSQL 18 offre la funzionalità documentata. Questa guida mantiene semplice la struttura delle tabelle, così che i dettagli della gerarchia delle partizioni non distolgano l’attenzione dal comportamento delle righe.

Anche la chiave referenziata deve avere una garanzia di unicità adeguata. In questo caso, articles.article_id è una chiave primaria. La colonna referenziante consente intenzionalmente NULL: con la regola predefinita MATCH SIMPLE, un riferimento nullo non deve corrispondere a un articolo. Aggiungi NOT NULL separatamente se ogni collegamento deve identificare un articolo. PostgreSQL 18: CREATE TABLE

Esegui un esempio sintetico completo sul tuo Mac

Ti servono un database PostgreSQL in esecuzione, una configurazione locale di connessione già funzionante e una versione di Node.js supportata da node-postgres. In una directory di progetto nel Terminale, installa il pacchetto con il comando seguente, poi salva il programma completo come not-valid-demo.mjs. La guida introduttiva ufficiale di node-postgres documenta l’installazione del pacchetto. Documentazione di node-postgres

npm install pg

Il programma usa new pg.Client(), che legge le variabili d’ambiente per la connessione a PostgreSQL, come PGHOST, PGPORT, PGDATABASE e PGUSER. Usa le impostazioni che già ti consentono di connetterti al database locale. node-postgres: connessione

Entrambe le tabelle sono temporanee ed esistono solo per la sessione di questa connessione. PostgreSQL richiede che le chiavi esterne delle tabelle temporanee facciano riferimento ad altre tabelle temporanee, quindi il programma crea entrambe le parti insieme. PostgreSQL 18: CREATE TABLE

Ogni operazione usa una query attesa separatamente. I valori vengono passati tramite parametri node-postgres come $1 e $2; i nomi delle tabelle e dei vincoli sono definiti direttamente nell’SQL. node-postgres: query

import pg from 'pg';

const client = new pg.Client({ connectionTimeoutMillis: 5000 });
const validate = `ALTER TABLE pg_temp.article_links
  VALIDATE CONSTRAINT article_links_article_fk`;

async function expectForeignKeyError(label, sql, values = []) {
  try {
    await client.query(sql, values);
  } catch (error) {
    if (error.code !== '23503' ||
        error.constraint !== 'article_links_article_fk') throw error;
    console.log(label + ': 23503 (expected)');
    return;
  }
  throw new Error(label + ': unexpectedly succeeded');
}

async function showStatus(label) {
  const result = await client.query(`
    SELECT conname, convalidated
    FROM pg_constraint
    WHERE conrelid = 'pg_temp.article_links'::regclass
      AND conname = 'article_links_article_fk'
      AND contype = 'f'
  `);
  console.log(label, result.rows);
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows);
  await client.query(`CREATE TEMP TABLE articles (
    article_id integer PRIMARY KEY,
    title text NOT NULL
  )`);
  await client.query(`CREATE TEMP TABLE article_links (
    link_id integer PRIMARY KEY,
    article_id integer,
    label text NOT NULL
  )`);
  await client.query(
    'INSERT INTO pg_temp.articles VALUES ($1, $2)',
    [1, 'Desk arrangement']
  );
  await client.query(`INSERT INTO pg_temp.article_links VALUES
    (10, 1, 'Matching article'),
    (11, 99, 'Older unmatched reference')`);
  await client.query(`ALTER TABLE pg_temp.article_links
    ADD CONSTRAINT article_links_article_fk
    FOREIGN KEY (article_id)
    REFERENCES pg_temp.articles (article_id) NOT VALID`);
  await showStatus('After adding');

  await expectForeignKeyError(
    'New unmatched reference',
    'INSERT INTO pg_temp.article_links VALUES ($1, $2, $3)',
    [12, 99, 'New reference']
  );
  await expectForeignKeyError(
    'Changed unmatched reference',
    'UPDATE pg_temp.article_links SET article_id = $1 WHERE link_id = $2',
    [98, 11]
  );
  await client.query(
    'UPDATE pg_temp.article_links SET label = $1 WHERE link_id = $2',
    ['Revised label', 11]
  );
  console.log('Label-only update succeeded');

  const unmatched = await client.query(`
    SELECT l.link_id, l.article_id, l.label
    FROM pg_temp.article_links AS l
    WHERE l.article_id IS NOT NULL
      AND NOT EXISTS (
        SELECT 1 FROM pg_temp.articles AS a
        WHERE a.article_id = l.article_id
      )
    ORDER BY l.link_id
  `);
  console.log('Unmatched references', unmatched.rows);
  await expectForeignKeyError('Validation before repair', validate);
  await showStatus('After failed validation');

  await client.query(
    'UPDATE pg_temp.article_links SET article_id = $1 WHERE link_id = $2',
    [1, 11]
  );
  await client.query(validate);
  await showStatus('After successful validation');
} finally {
  await client.end();
}

Esegui il programma salvato dalla stessa directory di progetto:

node not-valid-demo.mjs

Durante la preparazione, il codice JavaScript ha superato un controllo di sintassi Node. Nell’ambiente di ricerca non era possibile eseguire il database; i risultati seguenti sono quelli attesi in base al comportamento documentato, non un registro di esecuzione acquisito.

Come interpretare i risultati?

Per prima cosa, il vincolo dovrebbe comparire con convalidated impostato su false. Il catalogo definisce quel campo come indicatore della validazione del vincolo. La query filtra sia per tabella sia per nome del vincolo, perché i nomi dei vincoli non sono necessariamente univoci nel database. PostgreSQL: pg_constraint

Poi, sia l’inserimento del nuovo riferimento senza corrispondenza sia la modifica del riferimento a un ID inesistente dovrebbero produrre 23503. PostgreSQL identifica tale SQLSTATE come foreign_key_violation e consiglia di controllare i codici di errore invece di confrontare il testo localizzato dei messaggi. La funzione helper accetta solo il codice e il vincolo previsti; gli errori inattesi interrompono il programma. Codici di errore PostgreSQL

L’aggiornamento della sola etichetta lascia il collegamento 11 puntato all’articolo 99. La query diagnostica dovrebbe quindi restituire esattamente quel collegamento, ora con l’etichetta Revised label. La condizione esplicita IS NOT NULL esclude i riferimenti nulli facoltativi, mentre NOT EXISTS individua gli ID non nulli assenti da articles. Si tratta di risultati esemplificativi, ricavati dalle righe sintetiche fornite.

A quel punto, la validazione dovrebbe fallire. Dopo che la correzione imposta il riferimento del collegamento 11 all’articolo 1, la validazione dovrebbe riuscire e convalidated dovrebbe diventare true. VALIDATE CONSTRAINT controlla il vincolo precedentemente non validato eseguendo una scansione della tabella; non corregge i dati. PostgreSQL: ALTER TABLE

La correzione è una scelta editoriale esplicita per questa dimostrazione: entrambi i collegamenti devono fare riferimento a Desk arrangement. Per i tuoi dati sugli articoli, esamina ogni riferimento senza corrispondenza prima di decidere se correggere l’ID, aggiungere l’articolo mancante o rimuovere un collegamento obsoleto. Reindirizzare automaticamente ogni ID senza corrispondenza a un unico articolo nasconderebbe distinzioni che il database non può risolvere.

Come applicare la sequenza a tabelle popolate?

Procedi in tre fasi distinte: aggiungi e conferma il vincolo NOT VALID, esamina e correggi i riferimenti senza corrispondenza, poi esegui la validazione. L’aggiunta della chiave esterna acquisisce un lock SHARE ROW EXCLUSIVE su entrambe le tabelle. La validazione acquisisce SHARE UPDATE EXCLUSIVE sulla tabella referenziante e ROW SHARE su quella referenziata, consentendo i normali aggiornamenti concorrenti durante la scansione di validazione. L’acquisizione dei lock può comunque richiedere tempo. PostgreSQL: ALTER TABLE

Mantenere l’aggiunta iniziale e la validazione successiva in transazioni separate preserva lo scopo dell’approccio per fasi: la transazione iniziale può terminare prima che inizi la scansione più lunga. Pianifica l’aggiunta come un’operazione breve e considera la query diagnostica un aiuto per la pulizia, riservando alla validazione riuscita il ruolo di passaggio conclusivo.

La dimostrazione evita intenzionalmente un blocco di transazione esplicito, così da poter gestire singolarmente gli errori attesi delle istruzioni. Se adatti la sequenza alle transazioni node-postgres, esegui tu BEGIN, COMMIT e ROLLBACK, e mantieni tutte le istruzioni della transazione sullo stesso client. La documentazione di node-postgres specifica di non usare pool.query() per le istruzioni di transazione. node-postgres: transazioni

Considera insieme le tre osservazioni dell’esempio: le righe senza corrispondenza individuano il lavoro di correzione, il rifiuto dei nuovi riferimenti dimostra che il vincolo continua a essere applicato e convalidated = true registra l’avvenuta validazione. Avrai così un punto di completamento concreto per aggiungere la relazione a tabelle di articoli popolate.

Fonti