Vérification de l'intégrité des données

Aujourd'hui, on va commencer à comprendre comment les foreign keys nous aident à surveiller l'intégrité des données et à éviter les problèmes classiques liés aux données incohérentes ou incorrectes.

D'abord, voyons ce qu'on entend par "intégrité des données". Imagine que tu as une table avec des commandes (orders) et une table avec des clients (customers). Si une commande a un client qui n'existe pas dans la table des clients, c'est une violation de l'intégrité. C'est super important que toutes les données dans les tables liées soient logiquement cohérentes.

L'intégrité des données, ça veut dire :

• Pas de "liens vides" : si on fait référence à quelque chose dans une autre table, ce "quelque chose" doit toujours exister.
• Résistance aux erreurs de modification : si on supprime une valeur dans une table qui est référencée ailleurs, la base de données doit nous prévenir ou gérer ça correctement.

C'est justement pour ça qu'on utilise les foreign keys dans PostgreSQL.

Comment les foreign keys assurent l'intégrité des données ?

Quand tu crées une foreign key dans une table, PostgreSQL vérifie automatiquement :

1. Présence des données dans la table parente. Avant d'insérer ou de mettre à jour une ligne, PostgreSQL vérifie si la foreign key existe bien dans la table liée.
2. Suppression ou modification des données. Avant de supprimer ou modifier une ligne dans la table parente, PostgreSQL regarde si elle est référencée dans la table enfant.

Les foreign keys, c'est un peu comme un "garde du corps". Elles laissent pas passer des données foireuses et garantissent que les tables bossent ensemble selon les règles qu'on a posées.

Exemple : intégrité des données dans les tables étudiants et cours

Imaginons qu'on a deux tables — students et courses. Chaque étudiant peut s'inscrire à plusieurs cours. Pour représenter ce lien, on utilise la table enrollments. Voyons ce qui se passe si quelqu'un essaie d'inscrire un étudiant à un cours qui n'existe pas.

Étape 1. Créons trois tables liées :

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)
);

Ici :

• Dans la table enrollments, on a mis explicitement les foreign keys student_id et course_id qui pointent vers les primary keys des tables students et courses.

Vérifications typiques de l'intégrité des données

1. Vérification lors de l'insertion de données

Si on essaie d'insérer dans la table enrollments une ligne avec un student_id ou un course_id qui n'existe pas, ça va planter.

Exemple :

INSERT INTO enrollments (student_id, course_id)
VALUES (999, 1); -- Erreur ! L'étudiant avec l'ID 999 n'existe pas.

Message d'erreur :

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. Vérification lors de la suppression de données

Essayons de supprimer une ligne dans la table parente qui est référencée.

Exemple :

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

INSERT INTO enrollments (student_id, course_id)
VALUES (1, 1); -- Insertion réussie

DELETE FROM students WHERE student_id = 1; -- Erreur, parce que l'étudiant est encore inscrit à un cours !

Message d'erreur :

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".

Pour supprimer correctement dans ces cas-là, on utilise les stratégies CASCADE, SET NULL ou RESTRICT dont on a déjà parlé.

Exemples d'utilisation des foreign keys pour vérifier l'intégrité

Exemple 1 : Protection automatique contre les données incorrectes

Grâce aux foreign keys, PostgreSQL empêche automatiquement l'insertion de données "qui n'existent pas" :

-- Essayons d'ajouter des étudiants qui n'existent pas à un cours :
INSERT INTO enrollments (student_id, course_id)
VALUES (42, 1); -- Erreur ! L'étudiant avec l'ID 42 n'existe pas.

Ça garantit qu'un étudiant ne pourra pas s'inscrire à un cours s'il n'existe pas dans la table students.

Exemple 2 : Suppression de données avec ON DELETE CASCADE

Si la foreign key est configurée avec ON DELETE CASCADE, alors quand tu supprimes une ligne dans la table parente, les données liées dans la table enfant seront supprimées aussi.

ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey; -- On enlève l'ancienne 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; -- Maintenant, les lignes dans enrollments seront supprimées aussi

Exemple 3 : Gérer les modifications avec ON UPDATE

Si la foreign key est configurée avec ON UPDATE CASCADE, alors quand tu modifies la valeur dans la table parente, PostgreSQL mettra à jour automatiquement la table enfant.

-- On configure la foreign key pour que les changements dans la clé parente soient répercutés dans la table enfant :
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;

-- On modifie l'identifiant de l'étudiant :
UPDATE students SET student_id = 10 WHERE student_id = 1;

-- Maintenant, dans la table enrollments, student_id sera aussi mis à jour à 10.

Tester l'intégrité des données

C'est toujours utile de tester comment les foreign keys réagissent dans différents scénarios :

1. Essaie d'insérer des données avec un student_id ou course_id incorrect.
2. Supprime des données dans students et regarde ce qui se passe dans enrollments.
3. Modifie des données dans la table students et vérifie que les lignes liées sont bien mises à jour.

Points particuliers avec les foreign keys

Parfois, il y a des situations qui peuvent te faire galérer :

• Pas d'index. Si la table parente (students, par exemple) n'a pas de colonne indexée sur laquelle on fait référence, PostgreSQL peut "essayer" de bosser plus lentement. Donc, c'est important que la primary key dans la table parente soit toujours un index.
• Références cycliques. Si deux tables se réfèrent l'une à l'autre, ça peut compliquer l'insertion des données. Dans ces cas-là, il faut bien réfléchir à la conception.
• Suppression de toutes les lignes. Si tu veux tout supprimer avec un delete en cascade, fais gaffe à la nature des données dans la table enfant pour éviter des surprises.

Pour éviter ces soucis, il faut bien concevoir tes tables et tester les règles de relations avant de les utiliser en prod.