Verifica dell'integrità dei dati

Oggi iniziamo a capire come le foreign key ci aiutano a tenere sotto controllo l'integrità dei dati e a prevenire i problemi tipici legati a dati incoerenti o sbagliati.

Prima di tutto, vediamo cosa intendiamo per "integrità dei dati". Immagina di avere una tabella con gli ordini (orders) e una tabella con i clienti (customers). Se un ordine ha un cliente che non esiste nella tabella dei clienti, questa è una violazione dell'integrità. È importante che tutti i dati nelle tabelle collegate siano logicamente coerenti.

L'integrità dei dati significa:

• Nessun "riferimento vuoto": se facciamo riferimento a qualcosa in un'altra tabella, quel "qualcosa" deve sempre esistere.
• Resistenza agli errori di modifica: se cancelliamo da una tabella un valore a cui fanno riferimento altri record, il database deve avvisarci o gestire correttamente la situazione.

Proprio per questo in PostgreSQL si usano le foreign key.

Come le foreign key garantiscono l'integrità dei dati?

Quando crei una foreign key in una tabella, PostgreSQL controlla automaticamente:

1. Presenza dei dati nella tabella padre. Prima di inserire o aggiornare un record, PostgreSQL verifica che la foreign key indicata esista nella tabella collegata.
2. Cancellazione o modifica dei dati. Prima di cancellare o aggiornare un record nella tabella padre, PostgreSQL controlla che non ci siano record nella tabella figlia che fanno riferimento a quel valore.

Le foreign key sono una specie di "guardiano". Non lasciano passare dati sbagliati e garantiscono che le tabelle interagiscano secondo le regole stabilite.

Esempio: integrità dei dati nelle tabelle studenti e corsi

Supponiamo di avere due tabelle — students e courses. Ogni studente può iscriversi a più corsi. Per rappresentare questa relazione usiamo la tabella enrollments. Vediamo cosa succede se qualcuno prova a iscrivere uno studente a un corso che non esiste.

Passo 1. Creiamo tre tabelle collegate:

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),
    course_id INT REFERENCES courses(course_id)
);

Qui:

• Nella tabella enrollments abbiamo specificato chiaramente le foreign key student_id e course_id, che fanno riferimento alle primary key delle tabelle students e courses.

Controlli tipici di integrità dei dati

1. Controllo all'inserimento dei dati

Se proviamo a inserire nella tabella enrollments un record con student_id o course_id che non esistono, otterremo un errore.

Esempio:

INSERT INTO enrollments (student_id, course_id)
VALUES (999, 1); -- Errore! Lo studente con ID 999 non esiste.

Messaggio di errore:

ERROR:  insert or update on table "enrollments" violates foreign key constraint "enrollments_student_id_fkey"
DETAIL:  Key (student_id)=(999) is not present in table "students".

2. Controllo alla cancellazione dei dati

Proviamo a cancellare un record dalla tabella padre a cui fa riferimento un'altra tabella.

Esempio:

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO courses (title) VALUES ('Mathematics');

INSERT INTO enrollments (student_id, course_id)
VALUES (1, 1); -- Inserimento riuscito

DELETE FROM students WHERE student_id = 1; -- Errore, perché lo studente è ancora iscritto a un corso!

Messaggio di errore:

ERROR:  update or delete on table "students" violates foreign key constraint "enrollments_student_id_fkey" on table "enrollments"
DETAIL:  Key (student_id)=(1) is still referenced from table "enrollments".

Per cancellare correttamente i record in questi casi usiamo strategie come CASCADE, SET NULL o RESTRICT, di cui abbiamo già parlato.

Esempi di uso delle foreign key per il controllo dell'integrità

Esempio 1: Protezione automatica dai dati sbagliati

Con le foreign key PostgreSQL impedisce automaticamente l'inserimento di dati "inesistenti":

-- Proviamo ad aggiungere studenti inesistenti a un corso:
INSERT INTO enrollments (student_id, course_id)
VALUES (42, 1); -- Errore! Lo studente con ID 42 non esiste.

Questo garantisce che uno studente non possa iscriversi a un corso se non esiste nella tabella students.

Esempio 2: Cancellazione dei dati con ON DELETE CASCADE

Se la foreign key è impostata su cancellazione a cascata ON DELETE CASCADE, quando cancelli un record nella tabella padre anche i dati collegati nella tabella figlia verranno cancellati.

ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey; -- Rimuoviamo la vecchia foreign key

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON DELETE CASCADE;

DELETE FROM students WHERE student_id = 1; -- Ora verranno cancellati anche i record dalla tabella enrollments

Esempio 3: Gestione delle modifiche con ON UPDATE

Se la foreign key è impostata con ON UPDATE CASCADE, quando cambi il valore nella tabella padre PostgreSQL aggiorna automaticamente i dati nella tabella figlia.

-- Impostiamo la foreign key in modo che le modifiche alla chiave padre si riflettano automaticamente nella tabella figlia:
ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey;

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON UPDATE CASCADE;

-- Cambiamo l'identificatore dello studente:
UPDATE students SET student_id = 10 WHERE student_id = 1;

-- Ora anche nella tabella enrollments student_id sarà aggiornato a 10.

Testare l'integrità dei dati

È sempre utile testare come si comportano le impostazioni delle foreign key in vari scenari:

1. Prova a inserire dati con student_id o course_id sbagliati.
2. Cancella dati da students e guarda come si comporta la tabella enrollments.
3. Modifica dati nella tabella students e assicurati che i record collegati siano aggiornati.

Particolarità quando lavori con le foreign key

A volte ci sono situazioni che possono confondere:

• Assenza di indice. Se la tabella padre (students, per esempio) non ha una colonna indicizzata a cui si fa riferimento, PostgreSQL potrebbe "provare" a lavorare più lentamente. Quindi è importante che la primary key nella tabella padre sia sempre un indice.
• Riferimenti ciclici. Se due tabelle si riferiscono l'una all'altra, può essere complicato inserire i dati. In questi casi bisogna progettare con più attenzione.
• Cancellazione di tutti i record. Se vuoi cancellare tutti i record con cancellazione a cascata, considera la natura dei dati nella tabella figlia per evitare comportamenti inaspettati.

Per evitare questi problemi, è importante progettare bene le tabelle e testare le regole di relazione prima di usarle in un database reale.