SQL ON DELETE CASCADE: rimuovere automaticamente i dati dipendenti

Eliminare dati in SQL non significa solo rimuovere una singola riga. In un database relazionale, le tabelle sono spesso collegate tra loro e una cancellazione in una tabella può avere impatto su altre. Per gestire tutto in sicurezza, SQL usa i vincoli di chiave esterna per definire come devono comportarsi i dati correlati quando le righe vengono eliminate o aggiornate.

Una delle opzioni di eliminazione più potenti è il vincolo SQL ON DELETE CASCADE. Invece di bloccare una cancellazione o lasciare record orfani, questa regola indica al database di rimuovere automaticamente tutte le righe figlie dipendenti quando viene eliminata una riga padre di riferimento.

In questo tutorial, ti mostrerò come funziona il vincolo ON DELETE CASCADE, perché i database supportano le eliminazioni a cascata automatiche e quando è la scelta giusta rispetto a regole più restrittive come ON DELETE RESTRICT.  Se sei alle prime armi con SQL, inizia con il nostro corso Introduction to SQL, oppure con il corso Intermediate SQL se hai già un po’ di esperienza. 

Che cos’è ON DELETE CASCADE in SQL?

Per capire come funziona il vincolo di chiave esterna ON DELETE CASCADE, lascia che spieghi prima l’integrità referenziale e la clausola CASCADE.

Integrità referenziale e chiavi esterne

Nei database relazionali, le tabelle sono collegate tramite chiavi esterne, che collegano una colonna in una tabella (figlia) a una chiave primaria in un’altra tabella (padre). Le chiavi esterne impongono l’integrità referenziale per garantire che una riga figlia punti sempre a una riga padre valida. 

Senza questa garanzia, i database potrebbero facilmente ritrovarsi con record orfani, in cui le righe fanno riferimento a dati non più esistenti. Perciò esistono regole di eliminazione che indicano al database esattamente come gestire questi orfani prima ancora che si verifichino.

Cosa significa davvero CASCADE

In SQL, il vincolo ON DELETE CASCADE istruisce il database a eliminare automaticamente tutte le righe figlie che fanno riferimento alla riga padre eliminata. L’operazione viene attivata in modo ricorsivo attraverso le catene di chiavi esterne, eliminando i dati dipendenti in un’unica transazione atomica.

Per esempio, puoi eliminare un cliente nel database e l’operazione si propaga cancellando i suoi ordini, che a loro volta eliminano le righe degli articoli d’ordine in un’unica query.

Come funziona ON DELETE CASCADE nella pratica

Ora che hai capito cosa fa CASCADE, vediamo come il database applica la logica.

Relazioni tra tabelle padre e figlia

Come ormai sai, nelle relazioni tra tabelle una funge da padre, contenendo la chiave primaria, mentre un’altra funge da figlia, archiviando quella chiave come chiave esterna. Quando esegui un’istruzione DELETE sulla tabella padre, il motore del database prima controlla i vincoli di chiave esterna per identificare tutti i dipendenti tramite indicizzazione per la velocità.

Cosa succede quando si elimina una riga padre

Quando esegui un comando DELETE su una riga padre, il database sospende l’esecuzione finale per verificare le sue regole di chiave esterna nel seguente ordine:

• Identificazione: il motore consulta l’indice della chiave esterna per trovare tutte le righe nelle tabelle figlie che corrispondono all’ID in eliminazione.
• Esecuzione: prima (o durante) la rimozione della riga padre, il motore elimina le righe figlie identificate.
• Validazione: il database verifica che non rimangano righe orfane. Se tutte le eliminazioni riescono, la transazione viene finalizzata.

Nota che il processo sopra descritto avviene solo se hai definito il vincolo ON DELETE CASCADE, come vedremo più avanti nell’articolo.

ON DELETE CASCADE vs altre regole di eliminazione

In SQL esistono diverse regole di DELETE che comunicano al database come gestire i dati correlati tra tabelle diverse. In questa sezione confronteremo ON DELETE CASCADE con questi vincoli.

CASCADE vs RESTRICT

Dalla spiegazione precedente, vediamo che CASCADE elimina automaticamente tutte le righe figlie dipendenti quando una riga padre viene rimossa, garantendo che non restino record orfani.

Tuttavia, il vincolo SQL RESTRICT blocca completamente l’operazione di eliminazione se esistono righe figlie che fanno ancora riferimento alla riga padre. Questa operazione evita perdite di dati accidentali. Nell’esempio seguente, il vincolo RESTRICT impedisce di eliminare un cliente se esistono ordini che fanno riferimento a quel cliente.

-- Parent table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Child table with ON DELETE RESTRICT
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
);Offerto da

CASCADE vs SET NULL e SET DEFAULT

Mentre CASCADE rimuove completamente le righe dipendenti, SET NULL e SET DEFAULT preservano le righe figlie nei seguenti modi:

Quando definisci il vincolo come ON DELETE SET NULL, la riga padre viene eliminata, ma i valori della chiave esterna nella tabella figlia sono impostati a NULL.

Nell’esempio seguente, eliminare un cliente non rimuove gli ordini associati. Al contrario, la colonna customer_id nella tabella orders viene automaticamente impostata su NULL. In questo modo si preservano i record degli ordini interrompendo la relazione con il cliente eliminato.

-- Child table using ON DELETE SET NULL
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NULL,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET NULL
);Offerto da

Allo stesso modo, con l’opzione SET DEFAULT, eliminare un cliente non rimuove gli ordini correlati. Invece, il customer_id nella tabella orders viene reimpostato al suo valore predefinito. Questo mantiene intatti i record degli ordini assegnando loro un segnaposto predefinito.

-- Child table using ON DELETE SET DEFAULT
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT DEFAULT 0,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET DEFAULT
);Offerto da

Creare chiavi esterne con ON DELETE CASCADE

Per usare il vincolo ON DELETE RESTRICT in SQL, devi specificarlo al momento in cui stabilisci la relazione di chiave esterna tra le tabelle padre e figlia.

CASCADE negli schemi di tabella

Puoi definire la regola ON DELETE CASCADE in SQL sia durante la creazione delle tabelle, sia modificando tabelle esistenti.

L’esempio seguente mostra come applicare il vincolo di chiave esterna SQL ON DELETE CASCADE durante la definizione di una nuova tabella. Con CASCADE, l’eliminazione di un reparto rimuove automaticamente tutti i dipendenti associati a quel reparto.

-- Stores department details
CREATE TABLE departments (
    department_id INT PRIMARY KEY, 
    department_name VARCHAR(100)
);

-- Automatically deletes employees when their department is deleted
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,      -- Unique employee identifier
    department_id INT, 
    CONSTRAINT fk_employees_departments
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE CASCADE
);Offerto da

Se la tabella esiste già, puoi aggiungere un vincolo di chiave esterna con ON DELETE CASCADE. Di conseguenza, se elimini un reparto, vengono automaticamente eliminati tutti i record dei dipendenti correlati.

-- Adds a foreign key constraint after the table already exists
ALTER TABLE employees
ADD CONSTRAINT fk_employees_departments
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE CASCADE;    -- Deletes employees when the related department is deletedOfferto da

Catene a cascata e eliminazioni multilivello

Una delle caratteristiche potenti di CASCADE è che le eliminazioni non si fermano a una singola relazione. Se una tabella figlia è anche padre di un’altra tabella, la cancellazione può propagarsi su più livelli, chiamati catene a cascata. Ad esempio, eliminare un utente potrebbe eliminare gli ordini, che poi eliminano gli articoli d’ordine, che a loro volta potrebbero eliminare le righe di audit correlate.

Per questo è importante documentare e rivedere con attenzione gli schemi che usano CASCADE. Poiché una singola istruzione di eliminazione può influire su molte tabelle, dovresti comprendere l’intera catena di dipendenze prima di abilitare il comportamento a cascata.

Casi d’uso comuni di ON DELETE CASCADE

Il vincolo ON DELETE CASCADE in SQL è importante negli scenari in cui i dati nella tabella figlia non hanno valore senza il record padre. Vediamo la sua applicazione nella gestione di database reali.

Pulizia automatica dei dati dipendenti

Puoi usare ON DELETE CASCADE per ripulire i dati laddove la pulizia manuale sarebbe inefficiente. Questo include tabelle come log, record storici, audit trail o tabelle di join molti-a-molti che spesso esistono solo a supporto di un record padre. Per esempio:

• Eliminare un utente dovrebbe rimuovere la sua cronologia di accesso
• Eliminare un ordine dovrebbe rimuovere i relativi articoli d’ordine
• Eliminare un prodotto dovrebbe rimuovere le voci in una tabella di mappatura prodotto-categoria

Semplificare la gestione del ciclo di vita dei dati

Puoi anche usare CASCADE per spostare le regole del ciclo di vita dei dati nel database. Invece di affidarti al codice dell’applicazione per trovare i record correlati, eliminarli nell’ordine corretto e gestire casi limite ed errori, il database applica queste regole in modo coerente e atomico.

In questo modo, CASCADE assicura che, indipendentemente da come viene attivata un’eliminazione — tramite un’applicazione, uno script o una query di amministrazione — il risultato sia sempre coerente.

Rischi e insidie di ON DELETE CASCADE

Sebbene il vincolo ON DELETE CASCADE possa essere comodo, può risultare rischioso se usato in modo improprio. Capire le insidie comuni è importante per progettare vincoli CASCADE efficienti.

Cancellazioni di massa accidentali

Il rischio più comune con CASCADE sono le eliminazioni su larga scala non intenzionali. Una singola istruzione DELETE su una tabella padre può rimuovere silenziosamente centinaia o migliaia di righe correlate in più tabelle. Questa azione può portare a perdite di dati inattese difficili da recuperare se non conosci a fondo la relazione tra le tabelle. 

Come buona pratica, assicurati sempre di poter tracciare la relazione tra chiavi esterne nelle tabelle padre e figlie. Se progetti correttamente lo schema, saprai quali tabelle verrebbero interessate da una singola eliminazione.

Dipendenze nascoste in schemi complessi

Con la crescita dei database, le tabelle accumulano ulteriori chiavi esterne e la documentazione può non essere più allineata alla realtà. La sfida si presenta quando CASCADE viene implementato in tali contesti e le eliminazioni possono propagarsi attraverso catene di dipendenze profondamente annidate di cui sviluppatori o amministratori non sono a conoscenza.

Per esperienza, ti consiglio di abbinare CASCADE a una documentazione chiara dello schema e a revisioni periodiche delle relazioni di chiave esterna.

Prova il nostro skill track SQL Server for Database Administrators se progetti o mantieni regolarmente database nel tuo lavoro. 

Note e comportamenti specifici dei database

Sebbene il concetto di ON DELETE CASCADE sia standard nei database relazionali, ogni motore gestisce l’esecuzione sottostante con piccole differenze.

Comportamento di PostgreSQL, MySQL e SQL Server

PostgreSQL, MySQL e SQL Server tutti supportano ON DELETE CASCADE e lo implementano nello stesso modo, con le seguenti eccezioni:

• MySQL: CASCADE funziona bene con InnoDB, ma evita le cascate circolari

• PostgreSQL: il più flessibile e permissivo con le catene di eliminazioni a cascata

• SQL Server: più sicuro ma più restrittivo a causa delle regole sui percorsi di cascata multipli

Ambito della transazione e sicurezza del rollback

Le eliminazioni a cascata vengono eseguite nella stessa transazione dell’istruzione di eliminazione originale. Se il database incontra un errore durante l’eliminazione di una riga figlia, l’intera operazione fallisce. La riga padre non viene eliminata e le eventuali righe figlie già rimosse vengono ripristinate.

Questo comportamento transazionale fornisce una rete di sicurezza importante. Ti permette di testare, rivedere o annullare le eliminazioni a cascata prima di confermare.

Scegliere la strategia ON DELETE giusta

A questo punto sai che la scelta della strategia di eliminazione corretta dipende da come sono correlati i tuoi dati e da come vuoi che si comportino nel tempo. Considera le seguenti linee guida per fare la scelta di DELETE giusta per il tuo schema.

Quando CASCADE è la scelta giusta

Dovresti usare il vincolo ON DELETE CASCADE quando i dati figli dipendono completamente dal padre per significato o esistenza. Per esempio, puoi usarlo nei seguenti casi:

• Tabelle di join in relazioni molti-a-molti
• Dati temporanei o derivati, come log o voci di cronologia

In questi casi, la pulizia automatica garantisce coerenza riducendo la logica di eliminazione manuale richiesta nel livello applicativo.

Ti consiglio di seguire il corso Joining Data in SQL per imparare i diversi tipi di join in SQL e come lavorare con tabelle correlate nel database.

Quando evitare CASCADE

Dovresti invece evitare il vincolo ON DELETE CASCADE nello schema se i dati figli hanno un valore indipendente o devono essere conservati per motivi legali, di audit o analitici. Per esempio, potresti non usare CASCADE in:

• Registrazioni finanziarie e fatture
• Log di audit e dati di conformità, come i dati sanitari.

Per questi casi, consiglio di usare regole più restrittive, come RESTRICT, SET NULL o eliminazioni gestite dall’applicazione, per offrire maggiore controllo e visibilità.

Conclusione

Il vincolo SQL ON DELETE CASCADE è un potente strumento di automazione che preserva l’integrità referenziale rimuovendo automaticamente i dati dipendenti quando una riga padre viene eliminata. Se usato correttamente, semplifica la pulizia, riduce la logica di eliminazione manuale e garantisce un comportamento coerente tra applicazioni e script. Per evitare eliminazioni indesiderate, usa sempre CASCADE con cautela se non conosci a fondo le relazioni nello schema del database.

Ti consiglio di seguire il corso Database Design, dove imparerai a creare e gestire database e a scegliere il DBMS più adatto alle tue esigenze. Ti consiglio anche di provare il career track Associate Data Engineer in SQL per apprendere le basi dell’ingegneria dei dati e del data warehousing.