SQL DELETE: come rimuovere i dati in modo sicuro

Eliminare dati è un'operazione potenzialmente rischiosa in SQL. Un singolo comando scritto male può compromettere le relazioni tra tabelle o addirittura cancellare un intero dataset.

In questo tutorial ti mostrerò come funziona l'istruzione DELETE, come usarla correttamente e come evitare errori comuni, come cancellare un intero dataset, come appena accennato. 

Se sei alle prime armi con SQL, inizia con il nostro corso Introduction to SQL o con Intermediate SQL se hai già un po' di esperienza. 

Che cosa fa DELETE in SQL?

Prima di vedere la sintassi, è importante capire cosa fa effettivamente l'istruzione DELETE. Di seguito trovi le diverse modalità di rimozione dei dati in SQL.

SQL DELETE vs. rimozione delle righe a livello concettuale

L'istruzione DELETE rimuove una o più righe da una tabella preservandone la struttura. Quindi, lo schema della tabella, che include nomi delle colonne, tipi di dato, indici e vincoli, resta intatto. Dopo l'eliminazione, la tabella esiste ancora ed è pronta a ricevere nuovi dati.

Pensala come cancellare voci da un registro, non strapparne le pagine. Lo schema rimane lo stesso; vengono rimosse solo le righe selezionate.

SQL DELETE vs. DROP vs. TRUNCATE

Sebbene DELETE, DROP e TRUNCATE rimuovano tutti dati in qualche forma, servono a scopi molto diversi. La tabella seguente riassume l'uso di ciascuna istruzione:

Sintassi base di SQL DELETE

Ora che hai capito come funziona l'istruzione DELETE, vediamone la sintassi e come applicarla. 

Eliminare righe specifiche con WHERE

La clausola WHERE definisce l'ambito di un'operazione DELETE. La clausola WHERE è sempre necessaria perché indica esattamente al database quali righe devono essere rimosse.

-- Basic DELETE syntax
DELETE FROM table_name
WHERE condition;

Eliminare tutte le righe in una tabella

Se ometti la clausola WHERE nell'istruzione DELETE, SQL presuppone che tu voglia prendere di mira ogni singola riga nella tabella. Se lo fai per errore, il motore del database scorrerà ogni singolo record ed eliminerà tutto. Per ogni riga eliminata, viene creata una voce nel log delle transazioni. Se hai milioni di righe, questo può essere molto lento e causare il rigonfiamento dei file di log.

Buone pratiche per SQL DELETE

Poiché un'operazione DELETE può essere permanente, adottare pratiche di eliminazione sicure ti aiuterà a rallentare, verificare le intenzioni e pianificare come ripristinare se/quando qualcosa va storto.

Anteprima delle righe prima di eliminare

Prima di eseguire un'istruzione DELETE, visualizza sempre in anteprima le righe che stai per rimuovere usando un'istruzione SELECT con la stessa condizione WHERE che intendi usare nel tuo DELETE. Se l'istruzione SELECT restituisce troppe righe, o quelle sbagliate, puoi correggere la condizione prima di causare danni. 

Per esempio, la query qui sotto controlla le righe con ordini annullati prima del 2023-01-01, poi usa gli stessi filtri per eliminare questi record.

-- Step 1: Preview the targets
SELECT * FROM Orders 
WHERE Status = 'Cancelled' AND OrderDate < '2023-01-01';

-- Step 2: Once you've verified the list, convert to DELETE
DELETE FROM Orders 
WHERE Status = 'Cancelled' AND OrderDate < '2023-01-01';

Uso delle transazioni e del rollback

Se racchiudi l'operazione DELETE all'interno di una transazione, puoi eseguire un comando, vedere quante righe sono state interessate e poi decidere se rendere permanente la modifica o annullarla del tutto. Puoi quindi usare ROLLBACK per ripristinare i dati allo stato precedente.

La query seguente elimina temporaneamente le righe idonee e poi le ripristina, permettendoti di confermare l'impatto senza perdere dati.

BEGIN TRANSACTION;

DELETE FROM Users
WHERE LastLogin < '2020-01-01';

-- Inspect the affected row count
ROLLBACK;

Puoi quindi usare la seguente query per eliminare in modo permanente gli utenti che hanno effettuato l'ultimo accesso prima del 1° gennaio 2020.

BEGIN TRANSACTION;

DELETE FROM Users
WHERE LastLogin < '2020-01-01';

-- Confirm deletion of the required rows
COMMIT;

Tieni però presente che ROLLBACK è possibile solo se non hai ancora effettuato il commit della transazione.

SQL DELETE con join e sottoquery

Nei database reali, i dati raramente esistono in isolamento. Nella maggior parte dei casi potresti dover eliminare righe in base a valori archiviati in altre tabelle, account scaduti, record orfani o entità correlate che non sono più valide.

Eliminare righe basandosi su un'altra tabella

L'uso delle sottoquery è l'approccio più portabile e ampiamente supportato per eliminare righe in base a un'altra tabella. Funziona su quasi tutte le versioni di SQL. Per esempio, la query qui sotto elimina dalla tabella Users gli utenti inattivi che hanno account disattivati nella tabella Accounts.

-- Delete users whose accounts have been deactivated
DELETE FROM Users
WHERE AccountId IN (
    SELECT AccountId
    FROM Accounts
    WHERE Status = 'DEACTIVATED'
);

Puoi anche usare i join per eliminare righe in un database, ma la sintassi varia a seconda del database, come vedremo nella prossima sezione.

Sintassi DELETE specifica per database

Sebbene il concetto dell'istruzione DELETE sia lo stesso in SQL, i diversi database hanno sintassi diverse quando si usa JOIN per eliminare righe da una tabella in base a un'altra.

SQL Server colloca la tabella di destinazione dopo l'istruzione DELETE, quindi effettua il join in FROM. Nell'esempio seguente, l'alias dopo DELETE (u) specifica quale tabella viene eliminata.

-- SQL Server: Delete users linked to deactivated accounts
DELETE u
FROM Users u
JOIN Accounts a
  ON u.AccountId = a.AccountId
WHERE a.Status = 'DEACTIVATED';

In PostgreSQL, USING funziona come un JOIN. Vengono eliminate solo le righe della tabella di destinazione (Users).

-- PostgreSQL: Delete users linked to deactivated accounts
DELETE FROM Users
USING Accounts
WHERE Users.AccountId = Accounts.AccountId
  AND Accounts.Status = 'DEACTIVATED';

MySQL consente eliminazioni multi-tabella ma richiede la denominazione esplicita delle tabelle. Devi indicare la tabella da eliminare prima della clausola FROM.

-- MySQL: Delete users linked to deactivated accounts
DELETE u
FROM Users u
JOIN Accounts a
  ON u.AccountId = a.AccountId
WHERE a.Status = 'DEACTIVATED';

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.

SQL DELETE e integrità referenziale

Come ormai sappiamo, le tabelle raramente esistono isolate in un database relazionale. Spesso sono collegate tramite chiavi esterne. A causa di questi collegamenti, eliminare una riga in una tabella può avere conseguenze indesiderate.

Chiavi esterne ed errori di vincolo

Quando una tabella è riferita da una chiave esterna, il database applica regole su cosa succede quando provi a eliminare una riga padre. Per impostazione predefinita, la maggior parte dei database impedisce l'eliminazione se esistono ancora righe correlate in una tabella figlia.

Per esempio, se hai una tabella Orders che fa riferimento alla tabella Customers, l'eliminazione di un cliente che ha ancora ordini fallirà con un errore di vincolo. Questo protegge il database dal contenere record orfani che non hanno più un genitore valido.

Eliminazioni in cascata

Se vuoi eliminare automaticamente i dati correlati, devi definire esplicitamente una regola di cascata che istruisca il database su come gestire i dati collegati. ON DELETE CASCADE indica al database di eliminare automaticamente le righe figlie correlate quando viene rimossa una riga padre.

Per esempio, la query seguente indica al database di eliminare automaticamente tutte le righe nella tabella Orders che fanno riferimento a un cliente quando quel cliente viene eliminato dalla tabella Customers.

-- Parent table
CREATE TABLE Customers (
    CustomerId INT PRIMARY KEY,
    Name VARCHAR(100)
);

-- Child table with a cascading foreign key
CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,
    CustomerId INT,
    OrderDate DATE,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (CustomerId)
        REFERENCES Customers(CustomerId)
        ON DELETE CASCADE
);

Il vantaggio di questo approccio è che mantiene il database pulito senza richiedere più istruzioni DELETE manuali. Tuttavia, va usato con attenzione perché una singola eliminazione può innescare una reazione a catena su più tabelle, rimuovendo molti più dati del previsto. Per esempio, potresti eliminare una riga in una tabella Departments e cancellare accidentalmente 500 righe in una tabella Employees.

Considerazioni sulle prestazioni quando si eliminano dati

Usando l'istruzione DELETE, noterai che eliminare poche righe è istantaneo, ma diventa lento se hai milioni di righe. Considera le seguenti best practice per eliminare i record in sicurezza:

DELETE vs. TRUNCATE per tabelle grandi

Come visto in precedenza, DELETE e TRUNCATE rimuovono i record nei seguenti modi:

• DELETE: esamina ogni riga, verifica se soddisfa i criteri, la rimuove e registra la modifica nel log delle transazioni. È dispendioso in risorse e lento per dataset enormi. Consente il filtraggio con una clausola WHERE e supporta il rollback.

• TRUNCATE: rimuove tutte le righe in una volta con logging minimo. È significativamente più veloce ma non può essere filtrato, di solito non può essere annullato e viene bloccato dai vincoli di chiave esterna.

Eliminazioni a batch e operazioni di lunga durata

Se elimini milioni di righe in un'unica istruzione, potresti rallentare significativamente l'intera applicazione e persino causare il crash del database se non gestito correttamente. Per ridurre questo rischio, elimina sempre grandi dataset a batch.

Le eliminazioni a batch rimuovono un numero limitato di righe alla volta, ad esempio qualche migliaio per operazione. Questo permette al database di rimanere reattivo e rende più semplice monitorare l'avanzamento. Se qualcosa va storto, puoi interrompere il processo senza dover annullare un'enorme transazione.

Errori comuni con SQL DELETE

Di seguito sono riportati gli errori comuni a cui fare attenzione quando usi l'istruzione DELETE per rimuovere righe nelle tue tabelle:

Dimenticare la clausola WHERE

Dimenticare la clausola WHERE è l'errore più comune quando si usa DELETE. Senza di essa, l'istruzione rimuove ogni riga nella tabella. Assicurati sempre di usare la clausola WHERE per specificare le righe da eliminare. Come misura preventiva, adotta l'abitudine SELECT-prima-di-DELETE di cui abbiamo parlato per vedere le righe prima di eliminarle.

Supporre che DELETE sia reversibile

Un altro fraintendimento comune è che i dati eliminati possano sempre essere recuperati. Tieni presente che, per impostazione predefinita, l'istruzione DELETE, una volta effettuato il commit, contrassegna i dati come fisicamente rimossi dal disco, a meno che tu non abbia backup, repliche o log di audit.

Puoi “annullare” un comando DELETE solo se:

• È eseguito all'interno di una transazione.
• La transazione non è stata eseguita con commit.
• Il database e lo storage engine supportano il rollback.

SQL DELETE in ambienti di produzione

Nei sistemi di produzione, l'operazione DELETE riguarda più controllo, visibilità e responsabilità che sintassi. In questi ambienti, l'obiettivo non è solo rimuovere dati, ma farlo in modo sicuro e intenzionale.

Controllo degli accessi e permessi

In un contesto professionale, la possibilità di eliminare dati dovrebbe seguire il Principio del Privilegio Minimo. Non tutti gli utenti o gli account di servizio applicativi dovrebbero avere i permessi di DELETE. 

La maggior parte degli utenti “Read-Only” o degli strumenti di reportistica dovrebbe essere esplicitamente privata di questo diritto. Nella maggior parte delle aziende è prevista una “Revisione tra pari”, in cui una seconda persona deve verificare la clausola WHERE di uno script di eliminazione manuale prima che venga eseguito in produzione.

Ti suggerisco di provare il percorso di competenze SQL Server for Database Administrators se ti capita regolarmente di progettare o mantenere database nel tuo lavoro. 

Audit e soft delete

Molti sistemi di produzione evitano le eliminazioni definitive a causa dei rischi. Invece, usano le soft delete, in cui una riga è contrassegnata come eliminata. Ad esempio, puoi usare un timestamp deleted_at o un flag is_deleted piuttosto che rimuovere fisicamente un record.

Le soft delete rendono il recupero semplice, supportano l'auditing e preservano i dati storici per il debug e la conformità.

Conclusione

Il punto più importante è questo: definisci sempre chiaramente una clausola WHERE, visualizza in anteprima le righe interessate con SELECT e usa le transazioni quando possibile. Nei sistemi di produzione, ogni eliminazione dovrebbe essere intenzionale e verificabile. Tratta DELETE con la stessa cura che dedicheresti a una modifica dello schema o a un deployment, e rimarrà uno strumento utile anziché un errore costoso.

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 nostro percorso Associate Data Engineer in SQL per aprendere le basi dell'ingegneria dei dati e del data warehousing.