Guida alle relazioni tra tabelle nei database

Una relazione in un database collega le righe di una tabella alle righe di un'altra tramite una foreign key: una colonna che contiene la primary key della riga correlata. Le relazioni sono di tre tipi, uno a uno, uno a molti e molti a molti, e tutte e tre si costruiscono con le foreign key. Gli esempi funzionano in PostgreSQL, MySQL, SQLite e SQL Server.

Come creare relazioni in un database

Aggiungi alla tabella figlia una colonna per l'id del padre e dichiarala come foreign key, così il database rifiuta i valori che non corrispondono a una riga padre reale:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Fai una join sulla foreign key per mettere insieme le righe correlate. Il vincolo impedisce anche le righe che non puntano a nulla:

-- Input: customers
-- id | name
-- ---+-----------
-- 1  | Ana Lima
-- 2  | Ben Okafor

-- Input: orders
-- id | customer_id | total
-- ---+-------------+------
-- 10 | 1           | 25.00
-- 11 | 2           | 15.50
-- 12 | 1           | 40.00

SELECT c.name, o.id AS order_id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

-- Output:
-- name       | order_id | total
-- -----------+----------+------
-- Ana Lima   | 10       | 25.00
-- Ben Okafor | 11       | 15.50
-- Ana Lima   | 12       | 40.00

INSERT INTO orders (id, customer_id, total) VALUES (13, 99, 10.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- "orders_customer_id_fkey"

DELETE FROM customers WHERE id = 1;
-- ERROR: update or delete on table "customers" violates foreign key constraint
-- "orders_customer_id_fkey" on table "orders"

Regole da conoscere

• SQLite ignora le foreign key finché non le attivi. Esegui PRAGMA foreign_keys = ON; su ogni connessione, altrimenti l'INSERT errato qui sopra va a buon fine.
• Scrivi la foreign key su una riga a parte FOREIGN KEY (...) REFERENCES ..., come sopra. MySQL 8.0 ignora senza avvisare un REFERENCES scritto sulla colonna stessa (customer_id INTEGER REFERENCES customers(id)), quindi le righe errate entrano senza problemi.
• Decidi cosa succede quando elimini un padre. Di default il database si rifiuta, come sopra. Aggiungi ON DELETE CASCADE alla foreign key per eliminare anche le righe figlie, oppure ON DELETE SET NULL per mantenerle senza padre.
• Crea un indice sulla colonna della foreign key. La userai di continuo per join e filtri, e solo MySQL la indicizza in automatico: CREATE INDEX idx_orders_customer_id ON orders (customer_id);
• Dalle lo stesso tipo della chiave a cui punta, ad esempio INTEGER per un id INTEGER.
• Non salvare mai una lista di id in una sola colonna, come '1,3,5'. Il database non può verificarla né usarla nelle join. Quella è una relazione molti a molti e serve una tabella di collegamento (vedi sotto).

Sintassi alternativa

Dai un nome al vincolo, così i suoi errori sono più leggibili ed è più facile eliminarlo in seguito:

CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)

Per aggiungere una foreign key a una tabella esistente, usa ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id);. SQLite non lo supporta: crea una nuova tabella con la foreign key, copia le righe e scambia le tabelle.

Per gli id generati dal database, usa GENERATED ALWAYS AS IDENTITY (o SERIAL) in PostgreSQL, AUTO_INCREMENT in MySQL, IDENTITY(1,1) in SQL Server e INTEGER PRIMARY KEY in SQLite.

Quando usare le relazioni in un database?

Usa una relazione ogni volta che due tipi di entità sono collegati. Il tipo che ti serve dipende da quante righe possono stare su ciascun lato.

Dividere una tabella in due: uno a uno

Ogni riga corrisponde al massimo a una riga dell'altra tabella. Aggiungi una foreign key UNIQUE alla seconda tabella. Usala per spostare colonne opzionali o poco usate fuori da una tabella molto ampia, oppure per tenere i dati sensibili (utenti e relativi profili, dipendenti e relativi stipendi) sotto permessi separati.

Dare molti figli a una riga: uno a molti

La relazione più comune: un cliente ha molti ordini, un autore molti post. Metti una semplice foreign key sul lato “molti”. Il padre può anche essere la stessa tabella, come quando employees.manager_id punta a un altro dipendente.

Collegare molte righe a molte altre: molti a molti

Gli studenti seguono molti corsi e i corsi hanno molti studenti, quindi nessuna delle due tabelle può contenere l'id dell'altra. Aggiungi una tabella di collegamento con una foreign key verso ciascun lato e una primary key sulla coppia, così lo stesso collegamento non può essere salvato due volte. Le informazioni sul collegamento stesso, come un voto, vanno nella tabella di collegamento.

Trovare righe senza corrispondenza dall'altro lato

Una relazione mostra anche cosa manca. Una LEFT JOIN sulla foreign key mantiene tutte le righe della prima tabella e inserisce NULL dove non c'è corrispondenza, quindi filtrando per NULL trovi i clienti senza ordini o i corsi che nessuno ha seguito.

Esempi reali di relazioni nei database

Utenti e relativi profili

profiles.user_id è UNIQUE, quindi un utente non può avere un secondo profilo. La LEFT JOIN mantiene anche gli utenti che non ne hanno ancora scritto uno:

-- profiles.user_id is declared as: user_id INTEGER UNIQUE NOT NULL

-- Input: users
-- id | username
-- ---+---------
-- 1  | mia
-- 2  | raj
-- 3  | zoe

-- Input: profiles
-- id | user_id | bio
-- ---+---------+---------------------
-- 1  | 1       | Software developer
-- 2  | 2       | Marketing specialist

SELECT u.username, p.bio
FROM users u
LEFT JOIN profiles p ON p.user_id = u.id
ORDER BY u.id;

-- Output:
-- username | bio
-- ---------+---------------------
-- mia      | Software developer
-- raj      | Marketing specialist
-- zoe      | NULL

INSERT INTO profiles (id, user_id, bio) VALUES (3, 1, 'Second profile');
-- ERROR: duplicate key value violates unique constraint "profiles_user_id_key"

Dipendenti e relativi manager

Una relazione uno a molti all'interno di una sola tabella: manager_id è una foreign key verso employees.id. Fai una join della tabella con se stessa per affiancare ogni dipendente al suo manager:

-- Input: employees
-- id | name      | manager_id
-- ---+-----------+-----------
-- 1  | Maya Chen | NULL
-- 2  | Sam Ortiz | 1
-- 3  | Jo Kim    | 2
-- 4  | Lee Ross  | 2

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;

-- Output:
-- employee  | manager
-- ----------+----------
-- Maya Chen | NULL
-- Sam Ortiz | Maya Chen
-- Jo Kim    | Sam Ortiz
-- Lee Ross  | Sam Ortiz

Studenti e relativi corsi

La tabella di collegamento enrollments ha una foreign key verso ciascun lato e PRIMARY KEY (student_id, course_id). Fai una join attraverso di essa per elencare chi segue cosa:

-- Input: students
-- id | name
-- ---+------
-- 1  | Priya
-- 2  | Tomas
-- 3  | Leah

-- Input: courses
-- id | title
-- ---+----------------
-- 1  | Database Design
-- 2  | Web Development
-- 3  | Statistics

-- Input: enrollments
-- student_id | course_id | grade
-- -----------+-----------+------
-- 1          | 1         | A
-- 1          | 2         | B+
-- 2          | 1         | A-
-- 3          | 2         | A

SELECT s.name AS student, c.title AS course, e.grade
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
ORDER BY s.name, c.title;

-- Output:
-- student | course          | grade
-- --------+-----------------+------
-- Leah    | Web Development | A
-- Priya   | Database Design | A
-- Priya   | Web Development | B+
-- Tomas   | Database Design | A-

INSERT INTO enrollments VALUES (1, 1, 'B');
-- ERROR: duplicate key value violates unique constraint "enrollments_pkey"

Corsi senza iscritti

NOT EXISTS è un altro modo per trovare righe senza corrispondenza. Mantiene ogni corso che non ha righe nella tabella di collegamento:

-- Input: the courses and enrollments tables above

SELECT c.title
FROM courses c
WHERE NOT EXISTS (
  SELECT 1 FROM enrollments e WHERE e.course_id = c.id
);

-- Output:
-- title
-- ----------
-- Statistics

Approfondimenti sulle relazioni nei database

Su questo blog:

• Guida alle relazioni uno a uno: l'uno a uno in dettaglio
• Guida alle relazioni uno a molti: join, conteggi e aggregazioni sulle righe figlie
• Guida alle relazioni molti a molti: le tabelle di collegamento in dettaglio
• Guida alle JOIN SQL con diagrammi: tutti i tipi di join che userai tra tabelle in relazione
• Come usare le foreign key in PostgreSQL: le opzioni delle foreign key in PostgreSQL
• ON DELETE CASCADE in PostgreSQL: eliminare le righe figlie insieme al padre
• Primary key composte in SQL: la chiave a due colonne usata da una tabella di collegamento
• Guida a SQL CREATE TABLE: chiavi e vincoli quando crei una tabella

Documentazione ufficiale:

• PostgreSQL: foreign key
• MySQL: vincoli FOREIGN KEY
• SQLite: supporto alle foreign key
• SQL Server: vincoli di primary key e foreign key