Creazione di foreign key durante la creazione delle tabelle

Benvenuto a una delle lezioni chiave del nostro corso! Oggi parliamo di come creare foreign key in PostgreSQL. Questo argomento è fondamentale nel design dei database, perché sono proprio le foreign key che permettono di collegare le tabelle tra loro. Se ti senti già perso nel tuo futuro "SQL-città", immagina le foreign key come ponti che collegano diversi quartieri.

In parole semplici, una foreign key è una colonna (o un insieme di colonne) in una tabella che fa riferimento a una colonna (di solito la primary key) di un'altra tabella.

Per esempio, se hai due tabelle — students (studenti) e courses (corsi), una foreign key nella tabella courses può "puntare" a quale studente è iscritto al corso. Così si crea una relazione tra queste tabelle.

Perché è importante?

1. La foreign key aiuta a garantire l'integrità dei dati: non puoi inserire qualcosa in una tabella se non esiste nell'altra.
2. Rendono più semplice lavorare con i dati. Ad esempio, quando cancelli una riga in una tabella puoi impostare la cancellazione automatica delle righe collegate nell'altra.

Sintassi per creare una foreign key

Creare una foreign key in PostgreSQL è facile — basta un po' di magia SQL. Ecco la sintassi base:

CREATE TABLE tabella_dipendente (
    colonna_foreign_id DATA_TYPE REFERENCES tabella_genitore(colonna_id)
);

Meglio tuffarsi nei dettagli e vedere qualche esempio pratico.

Esempio 1: Tabelle students e courses

Immagina di voler creare una relazione tra studenti e corsi. Ogni corso deve essere collegato a uno studente. Basta eseguire questa query:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    student_id INT REFERENCES students(student_id)
);

Qui:

• Nella tabella students abbiamo creato una primary key PRIMARY KEY per identificare ogni studente.
• Nella tabella courses la colonna student_id è una foreign key FOREIGN KEY che fa riferimento alla colonna student_id nella tabella students.

Tabella students

Tabella courses

Nota importante

Quando aggiungi una foreign key, PostgreSQL crea automaticamente una regola che controlla che i valori nella foreign key corrispondano a valori esistenti nella tabella specificata. Se provi a inserire un valore sbagliato, il database ti darà errore.

Applicazione pratica: modello students, courses e enrollments

Vediamo un esempio più complesso con una relazione "molti-a-molti". Gli stessi studenti possono iscriversi a più corsi, e un corso può essere frequentato da molti studenti. Per creare questa relazione serve una tabella intermedia.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id), -- foreign key
    course_id INT REFERENCES courses(course_id)		-- foreign key
);

Qui la tabella enrollments collega le tabelle students e courses usando le foreign key student_id e course_id.

Inserimento dati

-- Aggiungiamo studenti
INSERT INTO students (name) VALUES ('Ivan Ivanov'), ('Maria Smirnova');

-- Aggiungiamo corsi
INSERT INTO courses (title) VALUES ('Matematica'), ('Fisica');

-- Iscriviamo gli studenti ai corsi
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1), (1, 2), (2, 1);

Selezione dati

Ora possiamo facilmente fare query per sapere quali corsi frequenta uno studente o quali studenti sono iscritti a un certo corso:

-- Corsi a cui è iscritto Ivan Ivanov
SELECT c.title
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
WHERE e.student_id = 1;

-- Studenti iscritti al corso "Matematica"
SELECT s.name
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
WHERE e.course_id = 1;

Funzionalità avanzate: ON DELETE e ON UPDATE

La foreign key deve anche gestire il comportamento della tabella quando vengono modificate o cancellate righe nella tabella genitore. Per questo si usano i modificatori ON DELETE e ON UPDATE. Ecco le opzioni principali:

• CASCADE: modifiche o cancellazioni nella tabella genitore vengono applicate automaticamente alle righe figlie.
• SET NULL: i valori della foreign key nella tabella figlia vengono impostati a NULL.
• RESTRICT: vieta la cancellazione o modifica dei dati se sono già usati nella tabella figlia dipendente.
• NO ACTION: praticamente uguale a RESTRICT, ma il controllo viene fatto dopo.

Esempio 2: Uso di ON DELETE CASCADE

Immagina di voler fare in modo che cancellando uno studente dalla tabella students vengano automaticamente cancellati tutti i suoi corsi dalla tabella courses. Ecco come si fa:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE
);

Ora, se cancelli uno studente dalla tabella students, tutte le righe collegate a quello studente nella tabella courses verranno cancellate. Per esempio:

INSERT INTO students (name) VALUES ('Ivan Ivanov');
INSERT INTO courses (title, student_id) VALUES ('Matematica', 1), ('Fisica', 1);

-- Cancellazione dello studente Ivanov
DELETE FROM students WHERE student_id = 1;

-- La tabella courses ora sarà vuota, perché tutti i corsi collegati a Ivanov sono stati cancellati

Ancora una volta, e in modo più dettagliato, vedremo questo argomento nella prossima lezione.

Processo di validazione dei dati usando le foreign key

Quando crei una foreign key, PostgreSQL fa il ruolo del portinaio severo, controllando ogni nuovo inserimento. Per esempio:

• Se provi a inserire una riga con un valore di foreign key che non esiste, riceverai un errore.
• Se cancelli una riga nella tabella genitore che è collegata ad altre tabelle (senza ON DELETE CASCADE), questo causerà una violazione dell'integrità dei dati.

Esempio: tentativo di inserire dati non validi

-- Tentativo di iscrivere un corso a uno studente che non esiste
INSERT INTO enrollments (student_id, course_id) VALUES (3, 1);
-- Errore: violazione del vincolo di foreign key

Errori tipici nella creazione delle foreign key

1. Mancanza di un indice sulla foreign key. PostgreSQL crea automaticamente un indice per la primary key, ma non per la foreign key. Se userai spesso la foreign key nelle condizioni WHERE, conviene creare l'indice a mano.
2. Ordine sbagliato nella creazione delle tabelle. Non puoi creare una foreign key che punta a una tabella che ancora non esiste.
3. Dimenticato il modificatore ON DELETE o ON UPDATE. Questo può portare a comportamenti strani quando modifichi i dati.

Ora che sai come creare le foreign key, hai uno strumento potente per costruire database strutturati e coerenti. Nella prossima lezione vedremo più in dettaglio le azioni ON DELETE CASCADE e ON UPDATE RESTRICT per gestire i dati collegati.