Le relazioni
Tabelle che si puntano a vicenda
- 15 minuti di lettura
Marta Villa, la capostazione di Piazza Query, ha una lavagna e un pennarello. Sulla lavagna ha disegnato le tabelle della Linea Menta come scatole, e tra una scatola e l'altra ha tirato delle frecce. «Quando non capisco un database», dice, «guardo dove puntano le frecce».
Questo capitolo insegna a leggere quelle frecce. Se non hai visto le tabelle e la chiave primaria, parti da Tabelle, tipi e psql. Se non sai ancora filtrare con WHERE e IN, da Leggere.
Perché le tabelle sono separate
Guarda due corse del M12, prese dalla tabella corse:
SELECT id, data, partenza, da_stazione_id, a_stazione_id FROM corse WHERE id IN (1, 3); id | data | partenza | da_stazione_id | a_stazione_id
----+------------+----------+----------------+---------------
1 | 2026-11-16 | 07:05:00 | 5 | 1
3 | 2026-11-16 | 05:42:00 | 1 | 5
(2 rows)La stazione di partenza non è scritta per nome: è un numero. Il 5 e il 1 sono gli id di Piazza Query e di Porta Codice, nella tabella stazioni.
Perché non scrivere «Piazza Query» in ogni corsa? Perché le corse sono venti, e un giorno diventeranno ventimila. Se il nome è scritto in ventimila righe, basta una riga con «Piazza Qeury» (un refuso) e la stessa stazione ha due nomi. Se Piazza Query cambia nome, devi trovarle tutte. Con un numero, il nome sta in un posto solo, la tabella stazioni, e tutte le corse lo ritrovano da lì.
Lo hai già fatto con i dizionari di Python: un dizionario di stazioni e le corse che ci puntano con la chiave.
stazioni = {1: "Porta Codice", 5: "Piazza Query"}
corsa = {"treno": "M12", "da": 5, "a": 1}
print(stazioni[corsa["da"]])
print(stazioni[corsa["a"]])
print(stazioni.get(99))Piazza Query
Porta Codice
NoneLa corsa tiene solo la chiave (5), il nome si va a cercare nell'altro dizionario. Se la chiave non c'è, get risponde None. Nel database una chiave che non c'è non deve proprio esistere: ora vedi come.
Il disegno
stazioni corse
┌────┬──────────────┐ ┌────┬──────────┬────────────────┬───────────────┐
│ id │ nome │ │ id │ treno_id │ da_stazione_id │ a_stazione_id │
├────┼──────────────┤ ├────┼──────────┼────────────────┼───────────────┤
│ 1 │ Porta Codice │ │ 1 │ 2 │ 5 │ 1 │
│ 5 │ Piazza Query │ │ 3 │ 2 │ 1 │ 5 │
└────┴──────────────┘ └────┴──────────┴────────────────┴───────────────┘
corsa 1: da_stazione_id = 5 ──▶ Piazza Query a_stazione_id = 1 ──▶ Porta Codice
corsa 3: da_stazione_id = 1 ──▶ Porta Codice a_stazione_id = 5 ──▶ Piazza Query- Una chiave esterna è una colonna che contiene la chiave primaria di un'altra tabella.
da_stazione_idè una chiave esterna: ogni valore è l'iddi una stazione. - La freccia va dalla chiave esterna alla chiave primaria. Si legge: «questa corsa parte da quella stazione».
- La colonna che punta ha di solito il nome della tabella a cui punta, con
_idin fondo:treno_idpunta atreni,stazione_idastazioni. Non è un obbligo:da_stazione_idemacchinista_idhanno un nome diverso dalla tabella (macchinista_idpunta apersonale).
REFERENCES: la regola scritta nella tabella
Una chiave esterna non è una convenzione: la scrive il CREATE TABLE, con la parola REFERENCES. Questo è il pezzo di linea_menta.sql che costruisce corse:
CREATE TABLE corse (
id SERIAL PRIMARY KEY,
treno_id INTEGER NOT NULL REFERENCES treni(id),
macchinista_id INTEGER NOT NULL REFERENCES personale(id),
da_stazione_id INTEGER NOT NULL REFERENCES stazioni(id),
a_stazione_id INTEGER NOT NULL REFERENCES stazioni(id),
data DATE NOT NULL,
partenza TIME NOT NULL,
ritardo_min INTEGER,
passeggeri INTEGER NOT NULL
);REFERENCES stazioni(id) si legge: «i valori di questa colonna devono essere valori della colonna id della tabella stazioni». Il tipo (INTEGER) è lo stesso della colonna a cui punta.
Non devi saperlo scrivere a memoria: devi saperlo leggere, e lo trovi anche in psql. Ecco \d corse:
Table "public.corse"
Column | Type | Collation | Nullable | Default
----------------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('corse_id_seq'::regclass)
treno_id | integer | | not null |
macchinista_id | integer | | not null |
da_stazione_id | integer | | not null |
a_stazione_id | integer | | not null |
data | date | | not null |
partenza | time without time zone | | not null |
ritardo_min | integer | | |
passeggeri | integer | | not null |
Indexes:
"corse_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"corse_a_stazione_id_fkey" FOREIGN KEY (a_stazione_id) REFERENCES stazioni(id)
"corse_da_stazione_id_fkey" FOREIGN KEY (da_stazione_id) REFERENCES stazioni(id)
"corse_macchinista_id_fkey" FOREIGN KEY (macchinista_id) REFERENCES personale(id)
"corse_treno_id_fkey" FOREIGN KEY (treno_id) REFERENCES treni(id)Le righe nuove sono quelle sotto Foreign-key constraints (vincoli di chiave esterna). Ognuna dice: FOREIGN KEY (colonna) REFERENCES tabella(colonna). Quattro righe, quattro frecce: dalla corsa al treno, al macchinista, alla stazione di partenza e alla stazione di arrivo.
E guarda dall'altra parte. \d stazioni, in fondo:
Referenced by:
TABLE "corse" CONSTRAINT "corse_a_stazione_id_fkey" FOREIGN KEY (a_stazione_id) REFERENCES stazioni(id)
TABLE "corse" CONSTRAINT "corse_da_stazione_id_fkey" FOREIGN KEY (da_stazione_id) REFERENCES stazioni(id)
TABLE "oggetti_smarriti" CONSTRAINT "oggetti_smarriti_stazione_id_fkey" FOREIGN KEY (stazione_id) REFERENCES stazioni(id)
TABLE "personale" CONSTRAINT "personale_stazione_id_fkey" FOREIGN KEY (stazione_id) REFERENCES stazioni(id)
TABLE "vendite" CONSTRAINT "vendite_stazione_id_fkey" FOREIGN KEY (stazione_id) REFERENCES stazioni(id)Referenced by vuol dire «puntata da»: le tabelle che hanno una freccia verso questa. Le stazioni sono puntate dalle corse (due volte), dagli oggetti smarriti, dal personale e dalle vendite. Quando una tabella ha dei «Referenced by», è una tabella madre; la tabella che ha le «Foreign-key constraints» è la figlia.
Cosa rifiuta PostgreSQL
La chiave esterna non è solo un disegno: PostgreSQL la fa rispettare, in due modi.
Primo: non puoi puntare a una riga che non c'è. Provi a registrare una corsa con il treno 99:
INSERT INTO corse (treno_id, macchinista_id, da_stazione_id, a_stazione_id, data, partenza, passeggeri)
VALUES (99, 4, 1, 5, '2026-11-19', '05:42', 50);ERROR: insert or update on table "corse" violates foreign key constraint "corse_treno_id_fkey"
DETAIL: Key (treno_id)=(99) is not present in table "treni".Si legge dal fondo: Key (treno_id)=(99) is not present in table "treni", cioè «il 99 non c'è nella tabella dei treni». Non entra nessuna corsa con un treno fantasma.
Secondo: non puoi togliere una riga a cui altre puntano. Provi a cancellare Porta Codice:
DELETE FROM stazioni WHERE id = 1;ERROR: update or delete on table "stazioni" violates foreign key constraint "personale_stazione_id_fkey" on table "personale"
DETAIL: Key (id)=(1) is still referenced from table "personale".is still referenced from table "personale": ci sono ancora persone che lavorano a Porta Codice. Se la stazione sparisse, le loro righe punterebbero nel nulla. Prima si sistemano le righe che puntano, poi si cancella.
(Come si scrivono INSERT e DELETE, e come si sistema tutto questo, è il capitolo Scrivere. Qui guarda soltanto cosa risponde il database.)
Uno a molti
Una stazione ha tante corse che partono da lei. Ogni corsa parte da una stazione sola. Questo è il legame uno a molti, ed è il più comune di tutti.
una stazione tante corse
┌──────────────┐ ┌───────────────────────────┐
│ Porta Codice │◀─────│ corsa 3, da Porta Codice │
│ │◀─────│ corsa 6, da Porta Codice │
│ │◀─────│ corsa 9, da Porta Codice │
└──────────────┘ └───────────────────────────┘La regola, da imparare bene: la chiave esterna sta sempre dalla parte del «molti». La corsa ha da_stazione_id; la stazione non ha una colonna «le mie corse». Non avrebbe senso: una casella sola non può contenere tante corse. Chi è «uno» resta com'è, chi è «molti» si porta dietro il numero.
Lo stesso vale per personale.stazione_id (una stazione, tanto personale), per oggetti_smarriti.stazione_id e per corse.treno_id (un treno, tante corse).
Due frecce verso la stessa tabella
corse ha due chiavi esterne verso stazioni: da_stazione_id (dove parte) e a_stazione_id (dove arriva). Sono due colonne diverse con due significati diversi, e tutte e due puntano agli id delle stazioni.
Nella corsa 1 parti da 5 e arrivi a 1; nella corsa 3 è il contrario. Per sapere i nomi servono due ricerche, una per colonna. Lo rivedi nel capitolo JOIN, dove la stessa tabella entra due volte con due soprannomi.
La freccia vuota
Una chiave esterna può anche non puntare a niente. Non tutto il personale lavora in una stazione: Ernesto sta al deposito, Ivo in manutenzione. La colonna personale.stazione_id è l'unica chiave esterna senza not null: può restare vuota, cioè NULL.
SELECT nome, cognome, stazione_id FROM personale WHERE stazione_id IS NULL; nome | cognome | stazione_id
---------+----------+-------------
Ivo | Brambati |
Pietro | Galli |
Dario | Sala |
Ernesto | Pozzi |
(4 rows)La casella vuota vuol dire «nessuna stazione», e PostgreSQL la accetta: una freccia che non parte non va controllata. Ricorda dal capitolo Leggere: per cercare i vuoti si scrive IS NULL, mai = NULL.
SELECT nome, cognome FROM personale WHERE stazione_id = NULL; nome | cognome
------+---------
(0 rows)Zero righe, e nessun errore. È la trappola più silenziosa del capitolo.
Molti a molti: la tabella ponte
Un macchinista può avere due patenti (metro e tram). Una patente è presa da tanti macchinisti. Qui non c'è un «uno»: da tutte e due le parti ci sono molti. Dove metti la chiave esterna?
Né in personale (una casella non può contenere due patenti) né in patenti (né una patente tanti macchinisti). La soluzione è una terza tabella, la tabella ponte, che ha una riga per ogni coppia «questa persona ha questa patente». Nella Linea Menta si chiama personale_patenti:
CREATE TABLE personale_patenti (
personale_id INTEGER NOT NULL REFERENCES personale(id),
patente_id INTEGER NOT NULL REFERENCES patenti(id),
presa_il DATE NOT NULL,
PRIMARY KEY (personale_id, patente_id)
);personale personale_patenti patenti
┌────┬────────┐ ┌──────────────┬────────────┬────────────┐ ┌────┬────────┐
│ 4 │ Sergio │◀───────│ 4 │ 1 │ 2012-03-01 │───────▶│ 1 │ metro │
│ 4 │ Sergio │◀───────│ 4 │ 2 │ 2014-04-12 │───────▶│ 2 │ tram │
│ 11 │ Rita │◀───────│ 11 │ 1 │ 2014-12-01 │───────▶│ 1 │ metro │
└────┴────────┘ └──────────────┴────────────┴────────────┘ └────┴────────┘Nel disegno le righe di personale e di patenti si ripetono perché ogni freccia ne ha una in punta: nelle tabelle vere ognuna c'è una volta sola. Tre cose da notare:
- Le due chiavi esterne: una verso
personale, una versopatenti. La tabella ponte è «figlia» di tutte e due. - La chiave primaria doppia:
PRIMARY KEY (personale_id, patente_id). Né la persona né la patente sono uniche da sole (Sergio compare due volte, la patente metro quattro), ma la coppia sì. Con questa chiave la stessa patente non si può segnare due volte alla stessa persona. - La tabella ponte può avere colonne sue:
presa_ilnon è di Sergio e non è del tram, è della coppia «Sergio e tram».
La tabella ponte non ha un id: la sua chiave è già la coppia. E nota che la funicolare (patente 3) non compare in nessuna riga: nessuno ce l'ha. Il database non ha bisogno di una riga «nessuno», basta che non ci sia.
Se un giorno ti serve una tabella ponte tua, la forma è sempre questa: una colonna per ogni tabella che collega, ognuna con il suo REFERENCES, e la chiave primaria sulle due colonne insieme. Nella palestra ne costruisci una.
Seguire la freccia a mano
Ora sai leggere le frecce. Come si usano per rispondere a una domanda? Senza nessun comando speciale, a mano: prima trovi l'id, poi lo usi.
Marta vuole sapere chi lavora a Piazza Query. In personale non c'è il nome della stazione, c'è un numero. Primo passo: quale numero?
SELECT id FROM stazioni WHERE nome = 'Piazza Query'; id
----
5
(1 row)Secondo passo: quel numero, nel WHERE della tabella che punta.
SELECT nome, cognome, ruolo FROM personale WHERE stazione_id = 5; nome | cognome | ruolo
-------+---------+--------------
Marta | Villa | capostazione
Paolo | Riva | macchinista
(2 rows)Se gli id sono più di uno, IN li prende tutti: WHERE stazione_id IN (1, 5). Con la tabella ponte i passi diventano tre (patente → persone → nomi), e li vedi negli esercizi spiegati di questo capitolo.
Funziona, ma è una fatica: due query, un numero da ricopiare. Ed è proprio la fatica che il prossimo capitolo toglie: con la JOIN il database segue la freccia per te, in una query sola. Prima però bisognava capire la freccia, ed è fatto.
Ricorda
- Una chiave esterna è una colonna che contiene la chiave primaria di un'altra tabella. Si scrive
REFERENCES tabella(colonna)e in psql si legge sotto Foreign-key constraints (\d corse) e, dall'altra parte, sotto Referenced by (\d stazioni). - PostgreSQL non accetta una riga che punta a qualcosa che non c'è (
is not present in table) e non lascia cancellare una riga a cui altre puntano (is still referenced from table). - Uno a molti: la chiave esterna sta dalla parte del «molti» (
corse.da_stazione_id,personale.stazione_id). - Una tabella può avere due chiavi esterne verso la stessa tabella (
da_stazione_id,a_stazione_id). - Una chiave esterna può essere
NULL: nessuna freccia. Si cerca conIS NULL, mai= NULL. - Molti a molti: una tabella ponte con due chiavi esterne e la chiave primaria sulla coppia (
personale_patenti). - Seguire la freccia a mano: prima trovi l'id, poi lo usi nel
WHERE(conINse sono tanti). LaJOINfa lo stesso in una query sola: JOIN.