Vai al contenuto
code-to-code

Apro l'indice…

↑ ↓ per scegliere, Invio per aprire, Esc per chiudere

SQL3. Le relazionip. 46

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:

SQL
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.

Python
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
None

La 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'id di 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 _id in fondo: treno_id punta a treni, stazione_id a stazioni. Non è un obbligo: da_stazione_id e macchinista_id hanno un nome diverso dalla tabella (macchinista_id punta a personale).

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:

SQL
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:

SQL
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:

SQL
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.

SQL
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.

SQL
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:

SQL
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:

  1. Le due chiavi esterne: una verso personale, una verso patenti. La tabella ponte è «figlia» di tutte e due.
  2. 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.
  3. La tabella ponte può avere colonne sue: presa_il non è 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?

SQL
SELECT id FROM stazioni WHERE nome = 'Piazza Query';
 id 
----
  5
(1 row)

Secondo passo: quel numero, nel WHERE della tabella che punta.

SQL
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 con IS 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 (con IN se sono tanti). La JOIN fa lo stesso in una query sola: JOIN.