Vai al contenuto
code-to-code

Apro l'indice…

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

SQL3. Le relazionip. 53

Le relazioni, esercizi spiegati

Cinque esempi, una freccia alla volta

  • 20 minuti di lettura

Marta Villa, la capostazione di Piazza Query, vuole capire chi lavora dove e chi può guidare cosa. Cinque esempi, uno per ogni idea del capitolo. Ognuno ha lo stesso ritmo:

  1. Prevedi: leggi la domanda e scrivi su un foglio cosa risponderà il database, prima di aprire la risposta.
  2. In italiano: cosa chiede, a parole.
  3. Passo per passo: cosa succede, un pezzo alla volta.
  4. Cosa portiamo a casa: la cosa da ricordare.

Per provarli: psql -d sql_relazioni, poi incolli la query. Il database si crea con i comandi della pagina Palestra. Le idee sono nella pagina Teoria di questo capitolo.


A · Leggere la freccia di personale

Marta apre \d personale per capire a cosa è collegato il personale.

sql_relazioni=# \d personale

Prevedi: quante chiavi esterne ha personale? Quali tabelle puntano a personale? Quale colonna può restare vuota?

Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
                               Table "public.personale"
   Column    |  Type   | Collation | Nullable |                Default                
-------------+---------+-----------+----------+---------------------------------------
 id          | integer |           | not null | nextval('personale_id_seq'::regclass)
 nome        | text    |           | not null | 
 cognome     | text    |           | not null | 
 ruolo       | text    |           | not null | 
 badge       | text    |           | not null | 
 stazione_id | integer |           |          | 
 email       | text    |           |          | 
 assunto_il  | date    |           | not null | 
Indexes:
    "personale_pkey" PRIMARY KEY, btree (id)
    "personale_badge_key" UNIQUE CONSTRAINT, btree (badge)
Foreign-key constraints:
    "personale_stazione_id_fkey" FOREIGN KEY (stazione_id) REFERENCES stazioni(id)
Referenced by:
    TABLE "corse" CONSTRAINT "corse_macchinista_id_fkey" FOREIGN KEY (macchinista_id) REFERENCES personale(id)
    TABLE "personale_patenti" CONSTRAINT "personale_patenti_personale_id_fkey" FOREIGN KEY (personale_id) REFERENCES personale(id)
  • Una chiave esterna: stazione_id punta a stazioni(id).
  • Due tabelle puntano a personale: corse (con macchinista_id) e personale_patenti (con personale_id).
  • stazione_id non ha not null: può restare vuota. Lo stesso vale per email, ma quella non è una freccia.

In italiano. «Mostrami com'è fatta la tabella e come è legata alle altre.»

Passo per passo.

Dove guardiCosa leggi
Foreign-key constraintsLe frecce che partono da personale: quante sono, verso dove
Referenced byLe frecce che arrivano a personale, dalle tabelle figlie
Nullable della colonnaSe la freccia può mancare: vuota vuol dire «nessuna stazione»

Nota il nome di macchinista_id: non finisce con personale_id, ma punta a personale. Un macchinista è una riga di personale con il ruolo macchinista. Il nome della colonna dice che parte fa quella persona nella corsa; la riga REFERENCES dice dove sta.

Cosa portiamo a casa. Davanti a una tabella sconosciuta, leggi le due liste: chi punto io (Foreign-key constraints) e chi punta a me (Referenced by). In due minuti hai il disegno della lavagna.


B · Quello che il database non accetta

Marta vuole assumere Luca Neri, un nuovo capotreno, e per sbaglio scrive la stazione 99. Poi, per fare ordine, prova a togliere dalla tabella Piazza Query. (Come si scrivono questi comandi: Scrivere. Qui guardi solo cosa risponde il database.)

SQL
INSERT INTO personale (nome, cognome, ruolo, badge, stazione_id, assunto_il)
VALUES ('Luca', 'Neri', 'capotreno', 'B-17', 99, '2026-11-19');

Prevedi: la riga entra? E questo?

SQL
DELETE FROM stazioni WHERE id = 5;
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
ERROR:  insert or update on table "personale" violates foreign key constraint "personale_stazione_id_fkey"
DETAIL:  Key (stazione_id)=(99) is not present in table "stazioni".
ERROR:  update or delete on table "stazioni" violates foreign key constraint "personale_stazione_id_fkey" on table "personale"
DETAIL:  Key (id)=(5) is still referenced from table "personale".

Nessuno dei due comandi cambia niente.

In italiano. «Aggiungi una persona alla stazione 99.» «No, non c'è.» «Togli la stazione 5.» «No, ci lavora ancora qualcuno.»

Passo per passo.

Pezzo dell'erroreVuol dire
violates foreign key constraint "personale_stazione_id_fkey"È la regola della chiave esterna, quella che hai visto in \d personale
Key (stazione_id)=(99) is not present in table "stazioni"Il 99 non è tra gli id di stazioni: la freccia punta nel vuoto
Key (id)=(5) is still referenced from table "personale"Il 5 c'è, ma qualcuno ci punta ancora: tolto lui, resterebbero frecce nel vuoto

Il nome del vincolo (personale_stazione_id_fkey) è lo stesso in tutti e due gli errori: è la stessa regola che protegge in due direzioni.

Cosa portiamo a casa. L'errore di una chiave esterna si legge dalla riga DETAIL: dice quale valore dà fastidio e in quale tabella. «Is not present» è una freccia che non arriva, «is still referenced» è una freccia che arriva e non si può togliere.


C · Segui la freccia a mano

Marta vuole l'elenco di chi lavora a Piazza Query. In personale il nome della stazione non c'è: c'è stazione_id.

SQL
SELECT id FROM stazioni WHERE nome = 'Piazza Query';

Poi, con il numero che esce:

SQL
SELECT nome, cognome, ruolo FROM personale WHERE stazione_id = 5;

Prevedi: quale numero esce dalla prima? Quante righe e chi dalla seconda?

Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
 id 
----
  5
(1 row)
 nome  | cognome |    ruolo     
-------+---------+--------------
 Marta | Villa   | capostazione
 Paolo | Riva    | macchinista
(2 rows)
  • La prima dà l'id 5: il numero che le altre tabelle usano per Piazza Query.
  • La seconda dà due righe: Marta stessa e Paolo Riva, il macchinista che non ha ancora guidato.

In italiano. «Qual è il numero di Piazza Query? E chi, in personale, ha quel numero in stazione_id?»

Passo per passo.

PassoTabellaCosa fai
1stazioniWHERE nome = 'Piazza Query' trova la riga e ti dà il suo id
2personaleWHERE stazione_id = 5: tieni le righe che puntano a quell'id

Il passo 1 si fa dalla parte della chiave primaria, cercando per nome. Il passo 2 dalla parte della chiave esterna, cercando per numero. Ricopi il 5 a mano da una query all'altra: è questa la fatica.

Cosa portiamo a casa. Seguire la freccia a mano: prima trovi l'id nella tabella madre, poi lo usi nel WHERE della tabella figlia. Sempre in questo ordine, e mai il contrario: nel WHERE di personale non ha senso scrivere il nome della stazione, perché lì il nome non c'è.


D · Tanti id, con IN

Per i sessant'anni della linea, Marta vuole contattare il personale delle stazioni inaugurate il 7 dicembre 1966. Sono più di una.

SQL
SELECT id, nome FROM stazioni WHERE inaugurata_il = '1966-12-07';

Poi:

SQL
SELECT nome, cognome, stazione_id FROM personale WHERE stazione_id IN (1, 2, 6);

Prevedi: quali stazioni escono dalla prima? E quante persone dalla seconda? Se invece scrivessi stazione_id = 1?

Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
 id |     nome      
----+---------------
  1 | Porta Codice
  2 | Via Variabile
  6 | Vecchia Fiera
(3 rows)
  nome  | cognome | stazione_id 
--------+---------+-------------
 Lucia  | Ferri   |           1
 Nadia  | Russo   |           6
 Sergio | Colombo |           1
 Elena  | Marchi  |           2
 Anna   | Colombo |           2
 Bruno  | Fontana |           1
(6 rows)
  • Tre stazioni, quindi tre id: 1, 2 e 6.
  • Sei persone. Le righe escono nell'ordine della tabella, non in quello della lista dentro IN: senza ORDER BY l'ordine non è garantito.
  • Con stazione_id = 1 resterebbero tre persone: Lucia, Sergio e Bruno. Le altre tre sono in stazioni che non hai nominato.

In italiano. «Quali stazioni sono state inaugurate quel giorno? E chi lavora in una qualsiasi di queste?»

Passo per passo.

PassoCosa resta
FROM stazioni WHERE inaugurata_il = '1966-12-07'Le tre stazioni del 1966, con i loro id
Ricopio gli id: (1, 2, 6)Una lista di numeri
FROM personale WHERE stazione_id IN (1, 2, 6)Le righe del personale la cui freccia punta a una di quelle tre

Nota che Vecchia Fiera è chiusa, ma Nadia Russo ci lavora ancora: nel database una stazione chiusa è una riga come le altre.

Cosa portiamo a casa. Quando la prima query restituisce più id, il WHERE dell'altra usa IN. Dimenticare un id (o scrivere = al posto di IN) perde delle righe senza nessun errore: controlla sempre che gli id nella lista siano tanti quanti le righe della prima query.


E · Molti a molti, in tre passi

Marta vuole sapere chi ha la patente tram. Il legame persone-patenti è molti a molti: ci sono la tabella ponte personale_patenti e due tabelle madri. I passi sono tre.

Passo 1, l'id della patente:

SQL
SELECT id FROM patenti WHERE nome = 'tram';

Passo 2, le coppie nel ponte che portano quell'id:

SQL
SELECT personale_id FROM personale_patenti WHERE patente_id = 2;

Passo 3, i nomi:

SQL
SELECT nome, cognome FROM personale WHERE id IN (4, 10, 12, 9, 2);

Prevedi: quanti id escono dal passo 2? E quanti nomi dal passo 3?

Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
 id 
----
  2
(1 row)
 personale_id 
--------------
            4
           10
           12
            9
            2
(5 rows)
  nome   | cognome  
---------+----------
 Ivo     | Brambati
 Sergio  | Colombo
 Ernesto | Pozzi
 Tommaso | Greco
 Paolo   | Riva
(5 rows)
  • Il tram ha id 2.
  • Cinque coppie nel ponte portano il 2: cinque persone.
  • Cinque nomi, uno per id. Anche qui l'ordine è quello della tabella personale (Ivo, 2, viene per primo), non quello della lista.

In italiano. «Qual è il numero della patente tram? Chi ha quel numero nel ponte? Chi sono quelle persone?»

Passo per passo.

PassoTabellaChe id esce o entra
1patenticerchi per nome, esce l'id della patente: 2
2personale_patentiWHERE patente_id = 2, esce la colonna personale_id: 4, 10, 12, 9, 2
3personaleWHERE id IN (…), esce il nome di ognuno

Si passa da tre tabelle, e la tabella ponte sta in mezzo: è lei che sa chi ha cosa. Nel passo 3 la colonna giusta del WHERE è id (di personale), perché ormai hai in mano gli id delle persone.

Cosa portiamo a casa. Con una tabella ponte si segue la freccia due volte: dalla tabella madre al ponte, dal ponte all'altra tabella madre. Ricopiare a mano gli id funziona per cinque righe e diventa un incubo per cinquemila: è il motivo per cui esiste la JOIN, nel capitolo successivo.