Integrità referenziale in SQL

Nelle lezioni precedenti abbiamo lavorato principalmente con i dati contenuti in una singola tabella. Nei database relazionali, però, le informazioni vengono spesso distribuite tra più tabelle collegate tra loro.

Per esempio, invece di memorizzare in ogni ordine tutti i dati del cliente che lo ha effettuato, possiamo avere una tabella clienti e una tabella ordini, collegandole attraverso un identificatore comune. In questo modo, si evitano duplicati e si mantengono i dati più ordinati.

In questa lezione vedremo come funzionano le chiavi esterne (dette foreign key), l'integrità referenziale e INNER JOIN, che permette di recuperare dati appartenenti a più tabelle nella stessa query.

Tabelle collegate

Nei database relazionali è comune distribuire le informazioni in tabelle separate, collegandole attraverso delle chiavi.

Ad esempio, possiamo avere una tabella clienti e una tabella ordini:

La colonna id della tabella clienti identifica univocamente ogni cliente e rappresenta una chiave primaria.

La colonna cliente_id della tabella ordini, invece, indica a quale cliente appartiene ciascun ordine. Viene definita foreign key (chiave esterna) perché collega la tabella ordini alla tabella clienti, facendo riferimento alla sua chiave primaria id.

Integrità referenziale

Una foreign key può essere utilizzata per applicare l'integrità referenziale, cioè per garantire che le relazioni tra le tabelle rimangano coerenti.

Per esempio, se ordini.cliente_id fa riferimento a clienti.id, non dovrebbe essere possibile inserire un ordine associato a un cliente che non esiste.

Per creare una tabella ordini collegata a clienti, si usa:

CREATE TABLE ordini (
    id INT PRIMARY KEY,
    cliente_id INT,
    totale DECIMAL(10,2),
    FOREIGN KEY (cliente_id) REFERENCES clienti(id)
);

Vedremo come usare CREATE TABLE nella lezione su come creare le tabelle.

Recuperare dati da più tabelle

Una volta che le tabelle sono collegate, possiamo avere la necessità di recuperare informazioni presenti in entrambe. Per esempio, possiamo voler ottenere il nome del cliente insieme al totale dei suoi ordini.

Per farlo, utilizziamo INNER JOIN.

La sintassi da usare è: INNER JOIN tabellaB ON tabellaA.id = tabellaB.id.

Nell'esempio precedente degli ordini, usiamo:

SELECT clienti.nome, ordini.totale
FROM clienti
INNER JOIN ordini
    ON clienti.id = ordini.cliente_id;

La condizione ON clienti.id = ordini.cliente_id dice a MySQL come collegare le righe delle due tabelle. Per ogni ordine, MySQL cerca nella tabella clienti la riga il cui id corrisponde a cliente_id.

INNER JOIN restituisce soltanto le righe per cui esiste una corrispondenza in entrambe le tabelle.

Nel nostro esempio, se un cliente non ha effettuato ordini, non comparirà nel risultato della query con INNER JOIN. Allo stesso modo, un ordine senza un cliente corrispondente non verrebbe restituito.

Rinominare le tabelle

Quando lavoriamo con più tabelle è molto comune rinominarle con AS, che abbiamo già visto nella lezione sulla selezione dei dati.

Possiamo, ad esempio, scrivere:

SELECT c.nome, o.totale
FROM clienti AS c
INNER JOIN ordini AS o
    ON c.id = o.cliente_id;

In questo modo:

La query diventa più compatta, soprattutto quando le tabelle hanno nomi lunghi o quando dobbiamo effettuare più JOIN.

Possiamo anche omettere AS:

SELECT c.nome, o.totale
FROM clienti c
INNER JOIN ordini o
    ON c.id = o.cliente_id;

Filtrare i risultati di JOIN

INNER JOIN può essere combinato con WHERE per filtrare il risultato.

Per esempio, possiamo ottenere soltanto gli ordini superiori a 100 euro:

SELECT c.nome, o.totale
FROM clienti c
INNER JOIN ordini o
    ON c.id = o.cliente_id
WHERE o.totale > 100;

In questo esempio:

Collegare più di due tabelle

Un INNER JOIN non è limitato a due tabelle. Possiamo collegare tre o più tabelle nella stessa query.

Ad esempio, supponiamo di avere le tabelle:

Possiamo quindi ottenere informazioni provenienti da tutte e tre le tabelle:

SELECT c.nome, o.totale, p.nome AS prodotto
FROM clienti c
INNER JOIN ordini o
    ON c.id = o.cliente_id
INNER JOIN prodotti p
    ON o.prodotto_id = p.id;