Chiavi esterne in SQLite: REFERENCES, ON DELETE e PRAGMA

Una chiave esterna è un puntatore tra tabelle

Una chiave esterna (foreign key) è una colonna di una tabella il cui valore deve corrispondere a una riga di un'altra tabella. È il modo in cui i database relazionali dicono "questa riga di posts appartiene a quella riga di authors" senza copiare il nome e l'email dell'autore in ogni post.

Ecco l'esempio più piccolo possibile: una tabella genitore e una tabella figlia collegate da una FK:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO authors (name) VALUES ('Ada'), ('Boris');
INSERT INTO posts (title, author_id) VALUES ('Ciao', 1), ('Note', 2);

SELECT p.title, a.name
FROM posts p JOIN authors a ON a.id = p.author_id;

author_id INTEGER REFERENCES authors(id) è tutta la dichiarazione della chiave esterna. Dice: questa colonna contiene un id della tabella authors. Ora il database sa che le due tabelle sono collegate e, se il controllo è attivo, rifiuterà gli inserimenti che puntano ad autori inesistenti.

Le chiavi esterne sono disattivate di default

È il fatto più importante sulle chiavi esterne in SQLite, e sorprende tutti: SQLite legge le clausole REFERENCES ma non le fa rispettare a meno che tu non lo chieda. Il motivo è la compatibilità storica: i database più vecchi sono stati creati prima che la funzionalità esistesse.

Guarda cosa succede senza il controllo:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

-- l'autore 999 non esiste, ma SQLite lo accetta senza problemi:
INSERT INTO posts (title, author_id) VALUES ('Orfano', 999);

SELECT * FROM posts;

La riga orfana è entrata senza ostacoli. Per avere la protezione che ti serve davvero, esegui PRAGMA foreign_keys = ON; all'inizio di ogni connessione:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO posts (title, author_id) VALUES ('Orfano', 999);

Ora l'inserimento fallisce con FOREIGN KEY constraint failed. Il pragma vale per connessione, non per database: l'impostazione non viene salvata nel file. Ogni applicazione, ogni sessione della CLI, ogni fixture di test deve impostarlo. La maggior parte del codice in produzione esegue PRAGMA foreign_keys = ON; subito dopo aver aperto una connessione.

Cosa richiede davvero la clausola REFERENCES

La colonna che referenzi deve essere una PRIMARY KEY o avere un vincolo UNIQUE. È così che SQLite può garantire che la ricerca non sia ambigua. Anche i tipi dovrebbero essere compatibili: SQLite è permissivo sui tipi, ma mescolarli vuol dire andare in cerca di sorprese.

Puoi scrivere la FK in due modi. In linea sulla colonna:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    author_id INTEGER REFERENCES authors(id),
    title     TEXT
);

SELECT sql FROM sqlite_master WHERE name = 'posts';

Oppure come vincolo separato a livello di tabella, obbligatorio quando la chiave esterna comprende più colonne:

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE tags  (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL,
    tag_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

SELECT sql FROM sqlite_master WHERE name = 'post_tags';

Le due forme producono vincoli identici. Usa quella che si legge meglio per la tabella che stai scrivendo.

ON DELETE: cosa succede ai figli

Quando elimini una riga genitore, SQLite deve decidere cosa fare con i figli che puntano a essa. La regola la scegli con ON DELETE:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('A'), ('B') ON CONFLICT DO NOTHING;
INSERT INTO posts (title, author_id) VALUES ('A', 1), ('B', 1);

DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;

Eliminando Ada sono stati eliminati anche entrambi i suoi post. Le opzioni sono:

• CASCADE: elimina anche i figli. Va bene per i dati "posseduti", come i post di un autore o le voci di un ordine.
• SET NULL: mette a NULL la colonna della FK. Va bene quando i figli devono sopravvivere senza genitore (per esempio, i commenti di un utente eliminato diventano anonimi).
• SET DEFAULT: imposta la colonna della FK sul suo valore predefinito dichiarato.
• RESTRICT: blocca l'eliminazione se esistono figli. Fallisce subito, al momento dell'istruzione.
• NO ACTION: il predefinito. Nella maggior parte dei casi si comporta come RESTRICT (rimanda il controllo al commit, ma il risultato è lo stesso: non puoi lasciare figli orfani).

ON UPDATE funziona allo stesso modo per le modifiche alla chiave del genitore, anche se aggiornare le chiavi primarie è raro.

Foreign key constraint failed: cosa significa

Vedrai questo errore in due situazioni. La prima: inserisci o aggiorni un figlio con un valore che non ha un genitore corrispondente:

sqlite> INSERT INTO posts (title, author_id) VALUES ('Stray', 999);
Runtime error: FOREIGN KEY constraint failed

O l'autore 999 non esiste, o hai confuso i tipi delle colonne. Inserisci prima il genitore, oppure correggi il valore.

La seconda: elimini (o aggiorni) un genitore che ha ancora dei figli, quando la FK usa RESTRICT o NO ACTION:

sqlite> DELETE FROM authors WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed

Elimina prima i figli, oppure cambia la FK in ON DELETE CASCADE/SET NULL se è la cascata che vuoi davvero.

C'è anche un parente meno comune, FOREIGN KEY mismatch. Scatta quando la colonna referenziata non è una chiave primaria o univoca, oppure quando il numero di colonne non coincide. È un errore di schema, non di dati.

Aggiungere chiavi esterne a tabelle esistenti

ALTER TABLE in SQLite è limitato: puoi aggiungere una colonna con una chiave esterna, ma non puoi applicare una chiave esterna a una colonna che esiste già. La soluzione standard è ricostruire la tabella e rinominarla:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('Ciao', 1), ('Mondo', 1);

PRAGMA foreign_keys = OFF;
BEGIN;

CREATE TABLE posts_new (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO posts_new SELECT id, title, author_id FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

COMMIT;
PRAGMA foreign_keys = ON;

SELECT sql FROM sqlite_master WHERE name = 'posts';

Lo schema: disattiva il controllo, crea la nuova tabella con i vincoli che vuoi, copia i dati, elimina la vecchia tabella, rinomina. BEGIN/COMMIT rendono il tutto atomico. Riattiva il controllo alla fine e SQLite verificherà le righe esistenti rispetto ai nuovi vincoli: se ci sono dati non validi, però, la transazione è già stata confermata, quindi controlla prima se hai dubbi.

Esegui PRAGMA foreign_key_check; dopo la migrazione per confermare che non ci siano righe orfane.

Uno schema realistico

Mettiamo tutto insieme: un piccolo schema per un blog, con genitori, figli e una tabella di collegamento per i tag molti a molti:

PRAGMA foreign_keys = ON;

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

CREATE TABLE posts (
    id         INTEGER PRIMARY KEY,
    author_id  INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    title      TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    id   INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Tre cose da notare. author_id è NOT NULL: ogni post deve avere un autore. La FK posts → authors va in cascata, quindi eliminando un autore si cancellano i suoi post. La tabella di collegamento post_tags va in cascata da entrambi i lati, quindi rimuovendo un post o un tag le righe di collegamento vengono ripulite automaticamente.

Abitudini che ti evitano guai

• Imposta PRAGMA foreign_keys = ON; su ogni connessione. Rendilo parte della routine con cui la tua applicazione apre il database, non qualcosa da ricordare.
• Aggiungi un indice sulla colonna della FK. SQLite indicizza automaticamente la chiave del genitore, ma non quella del figlio, e ON DELETE CASCADE fa una ricerca sul figlio ogni volta che viene eliminato il genitore.
• Scegli ON DELETE in modo consapevole. Il predefinito (NO ACTION) è sicuro, ma significa che ti scontrerai con "constraint failed" ogni volta che provi a fare pulizia. Decidi cosa deve succedere e dichiaralo.
• Esegui PRAGMA foreign_key_check; dopo migrazioni o importazioni massive per scovare gli orfani prima che diventino bug.

Prossimo passo: INNER JOIN

Le chiavi esterne descrivono la relazione; le join sono il modo in cui interroghi i dati attraverso di essa. La prossima pagina tratta INNER JOIN: combinare righe di tabelle collegate e ottenere le colonne che ti servono da ciascuna.