Combina ON DELETE SET NULL e ON DELETE CASCADE assegnando ciascuna azione a una chiave esterna distinta. Questa guida usa articoli sintetici per mostrare come l’eliminazione di una raccolta possa preservare i relativi articoli, mentre l’eliminazione di un articolo rimuove le sue sezioni.

L’obiettivo è definire questo comportamento, verificare entrambi i percorsi di eliminazione dal Terminal di un Mac e ripetere i controlli con node-postgres. Gli esempi usano normali chiavi esterne e tabelle temporanee, quindi ogni esecuzione dispone delle proprie righe di esempio.

Quale relazione dovrebbe usare ciascuna azione di eliminazione?

Un’azione di eliminazione appartiene alla chiave esterna nella tabella che fa riferimento. SET NULL cancella il riferimento quando la riga referenziata scompare; CASCADE elimina la riga che fa riferimento. PostgreSQL indica le relazioni facoltative come un caso d’uso appropriato per SET NULL e i componenti dipendenti come un caso d’uso appropriato per CASCADE. Vincoli

In questo esempio, una raccolta raggruppa gli articoli, ma un articolo può esistere senza una raccolta. Una sezione appartiene a un solo articolo e non ha un ruolo indipendente. Queste sono scelte progettuali esplicite per l’esempio:

Chiave esterna Azione scelta Risultato previsto
demo_articles.collection_id fa riferimento a demo_collections.collection_id ON DELETE SET NULL La rimozione di una raccolta preserva i suoi articoli senza un riferimento alla raccolta.
demo_sections.article_id fa riferimento a demo_articles.article_id ON DELETE CASCADE La rimozione di un articolo rimuove le sue sezioni.

Il comportamento risultante dipende da dove inizia l’eliminazione. L’eliminazione di una raccolta modifica collection_id di un articolo; non elimina l’articolo. Di conseguenza, ON DELETE CASCADE della chiave esterna della sezione non ha alcuna eliminazione dell’articolo a cui reagire. L’eliminazione dell’articolo stesso attiva invece la cascata. Questo deriva dalle definizioni documentate delle due azioni. CREATE TABLE

Non inserire due chiavi esterne in conflitto sulla stessa relazione per esprimere «cancella il riferimento ed elimina la riga». Decidi se la riga che fa riferimento deve sopravvivere, poi scegli l’azione che attua questa decisione.

Crea un esempio completo sul tuo Mac

Ti servono un server PostgreSQL in esecuzione, un database esistente a cui puoi connetterti e psql disponibile nel Terminal. I comandi qui sotto usano il database locale esistente chiamato postgres; se necessario, sostituiscilo con il nome del tuo database esistente. L’installazione e la configurazione del server non rientrano in questa guida.

Salva quanto segue in un file di testo semplice chiamato setup.sql in una cartella di lavoro. Tutte e tre le tabelle sono temporanee: PostgreSQL le rimuove quando termina la sessione. Anche tutte le tabelle referenziate in questo esempio sono temporanee, poiché PostgreSQL non consente chiavi esterne tra tabelle temporanee e permanenti. CREATE TABLE

CREATE TEMP TABLE demo_collections (
    collection_id integer PRIMARY KEY,
    title text NOT NULL
);

CREATE TEMP TABLE demo_articles (
    article_id integer PRIMARY KEY,
    collection_id integer,
    title text NOT NULL,
    CONSTRAINT demo_articles_collection_fk
        FOREIGN KEY (collection_id)
        REFERENCES demo_collections (collection_id)
        ON DELETE SET NULL
);

CREATE TEMP TABLE demo_sections (
    section_id integer PRIMARY KEY,
    article_id integer NOT NULL,
    heading text NOT NULL,
    CONSTRAINT demo_sections_article_fk
        FOREIGN KEY (article_id)
        REFERENCES demo_articles (article_id)
        ON DELETE CASCADE
);

CREATE INDEX demo_articles_collection_idx
    ON demo_articles (collection_id);
CREATE INDEX demo_sections_article_idx
    ON demo_sections (article_id);

INSERT INTO demo_collections (collection_id, title) VALUES
    (1, 'Layout Notes'),
    (2, 'Color Notes');

INSERT INTO demo_articles (article_id, collection_id, title) VALUES
    (101, 1, 'Grid Spacing'),
    (102, 2, 'Color Pairings');

INSERT INTO demo_sections (section_id, article_id, heading) VALUES
    (1001, 101, 'Overview'),
    (1002, 101, 'Examples'),
    (1003, 102, 'Overview');

Nota che collection_id ammette valori null, mentre article_id nella tabella delle sezioni è obbligatorio. Un’azione SET NULL deve comunque rispettare gli altri vincoli della tabella. Gli indici espliciti coprono le colonne che fanno riferimento; PostgreSQL non crea automaticamente tali indici quando viene dichiarata una chiave esterna. Vincoli

Verifica entrambi i percorsi di eliminazione in un’unica sessione

Salva il blocco seguente come verify.sql accanto a setup.sql. È uno script psql completo: \ir include il file di configurazione relativo allo script di verifica. Esegui entrambi i file tramite questo script, così le tabelle temporanee rimangono nella stessa sessione. L’opzione -f esegue un file e ON_ERROR_STOP interrompe l’elaborazione dello script in caso di errore. psql

BEGIN;
\ir setup.sql

DELETE FROM demo_collections
WHERE collection_id = 1
RETURNING collection_id;

SELECT a.article_id,
       a.title,
       a.collection_id,
       c.title AS collection_title,
       count(s.section_id) AS section_count
FROM demo_articles AS a
LEFT JOIN demo_collections AS c
    ON c.collection_id = a.collection_id
LEFT JOIN demo_sections AS s
    ON s.article_id = a.article_id
GROUP BY a.article_id, a.title, a.collection_id, c.title
ORDER BY a.article_id;

DELETE FROM demo_articles
WHERE article_id = 101
RETURNING article_id;

SELECT section_id, article_id, heading
FROM demo_sections
ORDER BY section_id;

ROLLBACK;

Dal Terminal, spostati nella cartella che contiene i file ed esegui:

psql -X -v ON_ERROR_STOP=1 -d postgres -f verify.sql

I risultati previsti della prima query derivano dalle righe di esempio:

Articolo ID raccolta dopo l’eliminazione della raccolta Titolo della raccolta Numero di sezioni
101 NULL NULL 2
102 2 Color Notes 1

L’articolo 101 sopravvive con le sue due sezioni. Dopo la seconda eliminazione, la query delle sezioni dovrebbe restituire solo la sezione 1003, appartenente all’articolo 102. Questi due controlli distinguono il caso in cui un articolo viene preservato da quello in cui l’articolo e le sue righe dipendenti vengono eliminati.

La query di verifica usa deliberatamente LEFT JOIN. PostgreSQL mantiene una riga non corrispondente della tabella a sinistra e fornisce valori null per i campi mancanti della tabella a destra. Una join interna con le raccolte escluderebbe l’articolo 101 dopo che il suo riferimento diventa null, facendo scomparire dai risultati della query un articolo che è stato preservato. Espressioni di tabella

Le clausole RETURNING indicano le righe eliminate dalle tabelle di destinazione. Non forniscono un inventario complessivo di tutte le sezioni rimosse dalla cascata; la query separata delle sezioni verifica tale risultato. DELETE

L’ultimo ROLLBACK annulla le modifiche della dimostrazione. PostgreSQL tratta inoltre una singola istruzione come una transazione, quindi qui una transazione esplicita è utile per raggruppare configurazione, eliminazione, ispezione e pulizia. Transazioni

Esegui gli stessi controlli con node-postgres

Per la versione JavaScript, lascia invariato setup.sql. Per questo esempio usa Node.js 24 e installa nella cartella di lavoro il pacchetto documentato pg: node-postgres

npm install pg

Salva il programma completo seguente come verify.mjs accanto a setup.sql. Legge gli stessi dati di esempio sintetici, verifica che l’eliminazione della raccolta preservi entrambi gli articoli e tutte e tre le sezioni, quindi verifica che l’eliminazione di un articolo rimuova solo le sezioni corrispondenti.

Il programma usa un unico client connesso per tutta la transazione, come richiesto da node-postgres. I valori delle query vengono passati tramite l’interfaccia documentata dell’array di parametri usando $1. Transazioni, Query

import pg from 'pg';
import { readFile } from 'node:fs/promises';
import assert from 'node:assert/strict';

const { Client } = pg;
const client = new Client();
const setup = await readFile(
  new URL('./setup.sql', import.meta.url),
  'utf8'
);

await client.connect();
try {
  await client.query('BEGIN');
  await client.query(setup);

  const removed = await client.query(
    'DELETE FROM demo_collections WHERE collection_id = $1 RETURNING collection_id',
    [1]
  );
  assert.deepEqual(removed.rows, [{ collection_id: 1 }]);

  const articles = await client.query(
    'SELECT article_id, collection_id FROM demo_articles ORDER BY article_id'
  );
  assert.deepEqual(articles.rows, [
    { article_id: 101, collection_id: null },
    { article_id: 102, collection_id: 2 }
  ]);

  const before = await client.query(
    'SELECT section_id FROM demo_sections ORDER BY section_id'
  );
  assert.deepEqual(
    before.rows.map(row => row.section_id),
    [1001, 1002, 1003]
  );

  await client.query(
    'DELETE FROM demo_articles WHERE article_id = $1',
    [101]
  );
  const after = await client.query(
    'SELECT section_id FROM demo_sections ORDER BY section_id'
  );
  assert.deepEqual(after.rows, [{ section_id: 1003 }]);

  console.log('Expected SET NULL and CASCADE outcomes verified.');
  await client.query('ROLLBACK');
} catch (error) {
  try {
    await client.query('ROLLBACK');
  } catch (rollbackError) {
    console.error('Rollback failed:', rollbackError.message);
  }
  throw error;
} finally {
  await client.end();
}

Eseguilo sullo stesso database locale esistente:

PGDATABASE=postgres node verify.mjs

new Client() legge le variabili d’ambiente di connessione a PostgreSQL. Mantieni le eventuali impostazioni di connessione già necessarie per il tuo server locale; cambiare solo il nome del database non configura un server diverso. Connessione

Un’asserzione non riuscita avvia il percorso di rollback e genera un errore. Il messaggio di successo appare solo dopo il superamento di tutti i controlli sui risultati. Il programma esegue deliberatamente il rollback anche in caso di successo, così la dimostrazione può essere ripetuta.

La sintassi JavaScript è stata controllata durante la preparazione. Nell’ambiente di ricerca non era disponibile un eseguibile PostgreSQL, quindi gli esempi del database sono stati confrontati con la documentazione anziché eseguiti; le tabelle sopra riportano i risultati previsti.

Cosa cambia con le chiavi esterne composte?

L’esempio principale usa chiavi esterne a colonna singola. Una chiave esterna composta introduce un’altra decisione: l’eliminazione dovrebbe cancellare tutte le colonne che fanno riferimento oppure solo quella facoltativa? PostgreSQL 15 ha introdotto elenchi di colonne per le azioni ON DELETE SET delle chiavi esterne; le versioni precedenti interessavano tutte le colonne della chiave esterna. Note di rilascio di PostgreSQL 15

Per esempio, un riferimento composto a un articolo potrebbe contenere un identificatore di edizione obbligatorio e un identificatore facoltativo di un articolo correlato. In PostgreSQL 15 o versioni successive, ON DELETE SET NULL (related_article_id) può preservare l’identificatore dell’edizione cancellando al contempo il riferimento facoltativo all’articolo. Senza quell’elenco di colonne, SET NULL si applica a entrambe le colonne. Questa sintassi è un’estensione di PostgreSQL ed è disponibile per ON DELETE, non per ON UPDATE. CREATE TABLE

Anche l’impostazione parziale a null richiede una regola di corrispondenza compatibile. L’impostazione predefinita MATCH SIMPLE consente che una chiave esterna composta contenga un valore null; MATCH FULL rifiuta una combinazione di valori referenziati null e non null. Pertanto, mantenere un identificatore di edizione obbligatorio impostando a null solo l’identificatore dell’articolo correlato è incompatibile con MATCH FULL. Vincoli

In PostgreSQL 14, se rappresenta correttamente la relazione, usa uno schema con un riferimento facoltativo a colonna singola separato, come nell’esempio principale. Se la relazione composta deve rimanere, cancella esplicitamente il suo riferimento facoltativo prima di eliminare la riga referenziata, usando un’unica transazione. È una sequenza gestita dall’applicazione, poiché la sintassi dell’elenco di colonne non è supportata.

In PostgreSQL 18 esiste un altro limite documentato: le chiavi esterne temporali che usano PERIOD non supportano CASCADE né SET NULL. Le normali chiavi esterne mostrate qui sono il metodo supportato per queste azioni di eliminazione quando la relazione non prevede la copertura di durata. CREATE TABLE

Come si diagnostica un risultato inatteso?

Usa il percorso di eliminazione come sequenza diagnostica:

  1. Conferma la tabella che fa riferimento. Esamina \d demo_articles e \d demo_sections nella sessione che contiene le tabelle. Controlla le azioni effettive delle chiavi esterne anziché dedurle dai nomi delle colonne. PostgreSQL documenta \d come metodo per esaminare i dettagli delle tabelle e i nomi dei vincoli. Modificare le tabelle
  2. Controlla la possibilità di usare valori null e gli altri vincoli. Se l’eliminazione della raccolta non riesce, verifica che la colonna da cancellare ammetta valori null e che gli altri vincoli accettino la riga risultante. SET NULL non li ignora. Vincoli
  3. Esamina tutte le chiavi esterne pertinenti. Una relazione aggiuntiva potrebbe avere un’azione diversa. Se non specificata, l’azione predefinita è NO ACTION, che può impedire l’eliminazione quando rimangono riferimenti. CREATE TABLE
  4. Interroga la tabella di base prima di interpretare una join. Se l’articolo esiste ancora lì, controlla le join e i filtri che potrebbero escludere il suo nuovo riferimento null. Espressioni di tabella
  5. Esegui le istruzioni della transazione sullo stesso client. In un’applicazione con pool, acquisisci un unico client per l’intera transazione. node-postgres sconsiglia esplicitamente di usare pool.query() per le istruzioni della transazione. Transazioni

Per questo modello di articoli, il test di accettazione è concreto: rimuovendo la raccolta 1 l’articolo 101 e le sue sezioni restano intatti, mentre rimuovendo l’articolo 101 rimane solo la sezione dell’articolo 102. Quando adatti lo schema, mantieni insieme questi due controlli, così il comportamento previsto di sopravvivenza ed eliminazione resta visibile.

Fonti