Gli esempi SQL utilizzano PostgreSQL a meno che non sia specificato un altro dialetto. Brevi frammenti presuppongono che le tabelle a cui fanno riferimento esistano già. Convalida il DDL nel tuo database di destinazione prima di applicarlo.
Comprendere i pattern di relazione SQL
I database relazionali traggono la loro potenza dalle connessioni tra le tabelle. Che tu stia progettando una piccola applicazione o un data warehouse aziendale, la scelta del corretto modello di relazione determina l'efficienza con cui puoi archiviare, interrogare e gestire i tuoi dati. I tre modelli fondamentali—uno-a-uno, uno-a-molti, e molti-a-molti— coprono molte associazioni comuni; le relazioni ricorsive e di grado superiore richiedono un'ulteriore modellazione.
In questa guida analizziamo ogni pattern con esempi PostgreSQL, esploriamo il ruolo delle tabelle di giunzione e mostriamo come queste relazioni appaiono su un Diagramma Entità-Relazione (ERD). Se vuoi visualizzare il tuo schema proprio ora, apri l' Editor di Erd Go e incollare supportati CREATE TABLE definizioni per generare un diagramma.
Relazioni Uno-a-Molti
A uno-a-molti La relazione (1:N) è il pattern più comune nella progettazione di database. Significa che una singola riga nella genitore tabella può essere riferita da molte righe nella figlia tabella, e una chiave esterna figlia non null punta a un genitore. Una chiave esterna nullable consente un figlio senza genitore.
Esempio classico: Dipartimenti e Dipendenti
Un'azienda ha molti reparti e ogni reparto contiene più dipendenti. Tuttavia, ogni dipendente appartiene a esattamente un reparto in un dato momento.
CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
location VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
department_id INT NOT NULL,
hire_date DATE DEFAULT CURRENT_DATE,
CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES departments (id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);Dettagli chiave dell'implementazione
- La chiave esterna (
department_id) risiede sempre sul molti lato—ilemployeestabella in questo caso. ON DELETE RESTRICTimpedisce di rimuovere accidentalmente un dipartimento che ha ancora dipendenti assegnati.ON UPDATE CASCADEgarantisce che se la chiave primaria di un dipartimento cambia, tutte le righe dei dipendenti si aggiornano automaticamente.- Aggiunta di un indice su
department_idpuò aiutare le join e i filtri di dipartimento; conferma il beneficio con dati rappresentativi e un piano di esecuzione.
Interrogazione di dati uno-a-molti
-- All employees in the Engineering department
SELECT e.first_name, e.last_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Engineering';
-- Count employees per department
SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY headcount DESC;Altri esempi quotidiani di uno-a-molti includono autori → post del blog, clienti → ordini, e categorie → prodotti.
Relazioni Molti-a-Molti
A molti-a-molti La relazione (M:N) esiste quando le righe su entrambi i lati possono essere associati a più righe sull'altro lato. I database relazionali non possono rappresentare ciò direttamente con una singola chiave esterna, quindi introduciamo un tabella di giunzione (chiamato anche bridge, join, o associativa tabella).
Esempio classico: Studenti e Corsi
Uno studente può iscriversi a molti corsi e ogni corso può avere molti studenti. La tabella di giunzione enrollments si frappone tra essi e contiene le chiavi esterne per entrambi i lati.
CREATE TABLE students (
id SERIAL PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
enrolled_on DATE DEFAULT CURRENT_DATE
);
CREATE TABLE courses (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
credits SMALLINT NOT NULL DEFAULT 3,
department VARCHAR(100)
);
CREATE TABLE enrollments (
student_id INT NOT NULL REFERENCES students (id) ON DELETE CASCADE,
course_id INT NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
grade CHAR(2),
PRIMARY KEY (student_id, course_id)
);Perché una tabella di giunzione?
- Il chiave primaria composita (
student_id, course_id) consente una riga per ogni coppia studente-corso. Se è richiesta l'iscrizione ripetuta in più periodi, includi un periodo nella chiave aziendale o modella ciascuna iscrizione separatamente. - Puoi allegare attributi aggiuntivi alla relazione stessa, come
gradeoenrolled_at, che non appartengono a nessuna entità da sola. ON DELETE CASCADEsu entrambe le chiavi esterne significa che la rimozione di uno studente o di un corso pulisce automaticamente le righe di iscrizione.
Interrogazione di dati molti-a-molti
-- All courses for a specific student
SELECT c.title, c.credits, e.grade
FROM enrollments e
JOIN courses c ON c.id = e.course_id
WHERE e.student_id = 42;
-- Students enrolled in 'Database Systems'
SELECT s.full_name, s.email
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE c.title = 'Database Systems';Esempi reali di molti-a-molti includono utenti ↔ ruoli, prodotti ↔ tag, attori ↔ film, e ordini ↔ prodotti (tramite un order_items tabella di giunzione che memorizza anche quantità e prezzo).
Relazioni Uno-a-Uno
A uno-a-uno La relazione (1:1) significa che ogni riga nella Tabella A corrisponde al massimo a una riga nella Tabella B, e viceversa. Questo pattern è meno comune ma utile per suddividere colonne raramente accessibili o sensibili in una tabella separata.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(60) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE user_profiles (
user_id INT PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE,
bio TEXT,
avatar_url VARCHAR(500),
birthdate DATE,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Nota che user_profiles.user_id è sia il chiave primaria e il chiave esterna. Questo vincolo applica la mappatura uno a uno a livello di database: ogni utente può avere al massimo una riga di profilo.
Come appaiono le relazioni negli ERD
Un Diagramma Entità-Relazione utilizza linee e simboli tra le tabelle per trasmettere la cardinalità a colpo d'occhio. Le due notazioni più popolari sono Zampe di gallina e Chen notazione.
| Relazione | Simbolo a zampe di gallina | Come leggere |
|---|---|---|
| Uno-a-Uno | ||—|| | Esattamente uno su ciascun lato |
| Uno-a-Molti | ||—< | Uno a sinistra, molti a destra (estremità a zampa di gallina) |
| Molti-a-Molti | >—< | Molti su entrambi i lati; implementato tramite tabella di giunzione |
Questi simboli di testo sono una scorciatoia per la cardinalità massima. L'opzionalità richiede un marcatore aggiuntivo di zero o uno; una chiave esterna da sola non richiede che un genitore abbia dei figli.
Quando modelli una relazione molti-a-molti in uno strumento ERD, in genere vedi tre caselle: due tabelle entità e la tabella di giunzione tra di esse, ciascuna collegata da una linea uno-a-molti.
Incolla uno qualsiasi degli esempi SQL di questo articolo in Editor di Erd Go per vedere il diagramma generato automaticamente, completo di frecce per le chiavi esterne e indicatori di cardinalità.
Scelta del modello di relazione corretto
La selezione della cardinalità errata porta a duplicazione dei dati, query complesse e bug di integrità. Utilizza questo schema decisionale:
- Una riga sul lato A può essere correlata a molte righe sul lato B, ma non viceversa? → Uno-a-Molti. Inserisci la chiave esterna sul lato B.
- Le righe su entrambi i lati possono essere correlate a molte righe sull'altro? → Molti-a-Molti. Crea una tabella di giunzione con una chiave composita.
- La mappatura è rigorosamente 1:1 e hai bisogno della separazione delle tabelle? → Uno-a-Uno. Usa la stessa chiave primaria come chiave esterna.
In caso di dubbio, abbozza prima il tuo schema. Un ERD rapido rivela i problemi di relazione molto prima rispetto a quando ci si imbatte in errori di vincolo in produzione. Prova a costruire il tuo diagramma in Editor di Erd Go— richiede pochi secondi e può far risparmiare ore di debugging.



