DROP TABLE e ALTER TABLE in SQLite: modificare lo schema

Gli schemi cambiano. SQLite ti permette di cambiarli, quasi sempre.

Una volta che una tabella esiste, prima o poi vorrai rinominarla, aggiungere una colonna, eliminarne una o ristrutturare tutto. SQLite supporta direttamente i casi comuni con DROP TABLE e ALTER TABLE, e per tutto il resto ti offre una soluzione alternativa documentata.

Il problema: l'ALTER TABLE di SQLite è molto più limitato di quello di Postgres o MySQL. Sapere cosa può e non può fare, e conoscere lo schema di ricostruzione per ciò che non può fare, è quasi tutta l'abilità che serve qui.

DROP TABLE rimuove una tabella e tutto ciò che ha collegato

DROP TABLE elimina la tabella, le sue righe, i suoi indici e gli eventuali trigger definiti su di essa. Non si può annullare:

CREATE TABLE scratch (
    id INTEGER PRIMARY KEY,
    note TEXT
);

INSERT INTO scratch (note) VALUES ('temporary'), ('also temporary');

DROP TABLE scratch;

SELECT name FROM sqlite_master WHERE type = 'table';

La tabella non c'è più. Interrogarla ora solleverebbe no such table: scratch.

Se non hai la certezza che la tabella esista, cosa frequente negli script di setup, usa IF EXISTS, così l'istruzione non fa nulla in silenzio quando la tabella manca:

CREATE TABLE scratch (id INTEGER PRIMARY KEY);

DROP TABLE IF EXISTS scratch;
DROP TABLE IF EXISTS scratch;
SELECT 'both drops succeeded' AS result;

Senza IF EXISTS, il secondo drop darebbe errore. Con IF EXISTS, entrambi vengono eseguiti senza problemi.

Le chiavi esterne possono bloccare un DROP

Se l'applicazione delle chiavi esterne è attiva (PRAGMA foreign_keys = ON;) e un'altra tabella fa riferimento a quella che stai eliminando, il drop fallisce:

sqlite> PRAGMA foreign_keys = ON;
sqlite> DROP TABLE users;
Runtime error: FOREIGN KEY constraint failed

Hai alcune opzioni: eliminare prima la tabella che fa riferimento, eliminare le righe che fanno riferimento, oppure definire la chiave esterna con ON DELETE CASCADE quando la crei. SQLite non romperà in silenzio l'integrità referenziale al posto tuo.

ALTER TABLE: le quattro cose che può fare

L'ALTER TABLE di SQLite supporta esattamente quattro operazioni:

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT
);

-- 1. Rinominare la tabella
ALTER TABLE posts RENAME TO articles;

-- 2. Rinominare una colonna
ALTER TABLE articles RENAME COLUMN body TO content;

-- 3. Aggiungere una colonna
ALTER TABLE articles ADD COLUMN published_at TEXT;

-- 4. Eliminare una colonna (SQLite 3.35+)
ALTER TABLE articles DROP COLUMN published_at;

SELECT sql FROM sqlite_master WHERE name = 'articles';

Ognuna viene eseguita come singola istruzione. Le prime due sono praticamente gratuite: aggiornano solo lo schema. Anche ADD COLUMN è veloce: SQLite non riscrive la tabella, registra solo la definizione della nuova colonna. DROP COLUMN è più pesante: SQLite deve riscrivere ogni riga per rimuovere fisicamente i dati della colonna.

ADD COLUMN con un valore di default

Una nuova colonna su una tabella esistente parte da NULL per ogni riga, a meno che tu non le dia un valore di default:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);

INSERT INTO users (email) VALUES ('ada@example.com'), ('boris@example.com');

ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

SELECT * FROM users;

Entrambe le righe esistenti ricevono 'active'. Il default deve essere una costante: SQLite non ti permette di usare CURRENT_TIMESTAMP o altre espressioni non costanti come default in ADD COLUMN, perché ha bisogno di un valore che possa applicare a ogni riga esistente senza valutarlo riga per riga.

Se ti serve NOT NULL senza default, dovrai aggiungere la colonna ammettendo i null, riempirla con un UPDATE e poi ricostruire la tabella per aggiungere il vincolo. E questo ci porta ai limiti.

Cosa ALTER TABLE non può fare

Cose che funzionano in Postgres o MySQL ma non in SQLite:

• Cambiare il tipo di una colonna (ALTER COLUMN ... TYPE ...).
• Cambiare sul posto il default di una colonna.
• Aggiungere o togliere NOT NULL, CHECK, UNIQUE o PRIMARY KEY su una colonna esistente.
• Aggiungere una chiave esterna a una colonna esistente.
• Riordinare le colonne.

Provare una qualsiasi di queste operazioni ti dà un errore di sintassi. SQLite non ha affatto una clausola ALTER COLUMN. La risposta ufficiale è la stessa per tutte: ricostruire la tabella.

Lo schema di ricostruzione

Quando ALTER TABLE non può fare ciò che ti serve, crei una nuova tabella con lo schema che vuoi, copi i dati, elimini la vecchia e rinomini la nuova al suo posto. Racchiudi tutto in una transazione così l'operazione va a buon fine per intero o per niente:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT,
    age TEXT
);

INSERT INTO users (email, age) VALUES ('ada@example.com', '36'), ('boris@example.com', '41');

BEGIN;

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    age INTEGER CHECK (age > 0)
);

INSERT INTO users_new (id, email, age)
SELECT id, email, CAST(age AS INTEGER) FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

Ora users.age è un intero con un vincolo CHECK, e email è NOT NULL. I dati sono venuti dietro senza problemi.

Alcune cose da tenere a mente quando lo fai sul serio:

• Disattiva le chiavi esterne per tutta l'operazione. Se altre tabelle fanno riferimento alla tua, esegui PRAGMA foreign_keys = OFF; prima della transazione e PRAGMA foreign_keys = ON; dopo. Altrimenti il DROP TABLE fallirà. Il pragma non si può cambiare dentro una transazione, quindi impostalo fuori.
• Ricrea indici e trigger. Eliminare la vecchia tabella elimina anche i suoi indici e trigger. Aggiungili di nuovo sulla nuova tabella dopo averla rinominata.
• Controlla le viste. Le viste che fanno riferimento alla tabella puntano ancora al vecchio nome nel loro SQL salvato. Ricostruisci quelle che dipendono dalle colonne modificate.

Lo schema di ricostruzione è prolisso ma affidabile. È quello che fanno dietro le quinte strumenti di migrazione come Alembic e Rails quando lavorano con SQLite.

Eliminare più tabelle

Non esiste un'unica istruzione per eliminare più tabelle: esegui DROP TABLE per ognuna. Dentro una transazione, se vuoi raggrupparle:

CREATE TABLE a (id INTEGER);
CREATE TABLE b (id INTEGER);
CREATE TABLE c (id INTEGER);

BEGIN;
DROP TABLE a;
DROP TABLE b;
DROP TABLE c;
COMMIT;

SELECT name FROM sqlite_master WHERE type = 'table';

Racchiuderle in una transazione significa che o riescono tutti e tre i drop, o nessuno: utile quando smonti tabelle collegate che potrebbero fallire a metà per via delle chiavi esterne.

Cosa portarti a casa

• DROP TABLE rimuove una tabella e i suoi indici/trigger. Usa IF EXISTS per script idempotenti.
• ALTER TABLE fa solo quattro cose: rinominare la tabella, rinominare una colonna, aggiungere una colonna, eliminare una colonna.
• Per tutto il resto, cambi di tipo, nuovi vincoli, chiavi esterne su colonne esistenti, ricostruisci la tabella dentro una transazione.
• Durante la ricostruzione fai attenzione a chiavi esterne, indici, trigger e viste. Non seguono i dati in automatico.

Prossimo passo: inserire i dati

Hai passato un capitolo sulle tabelle e sui vincoli che le definiscono. È ora di riempirle: il prossimo capitolo inizia con INSERT, compresa la forma su più righe, i valori di default e il modo in cui SQLite gestisce gli insert che entrano in conflitto con i tuoi vincoli.