Contraintes de clé étrangère et actions référentielles (ON DELETE / ON UPDATE)

Les données qu'on utilise — category et item

Une contrainte de clé étrangère (la contrainte qui garantit que la valeur d'une colonne existe toujours comme clé primaire dans une autre table) est le mécanisme qui protège l'intégrité référentielle entre tables (l'état où chaque ligne référencée existe réellement).

Elle bloque des contradictions comme « un item pointe vers une catégorie inexistante » ou « supprimer une catégorie laisse ses items orphelins », côté base de données.

Avant les exercices, jette un œil aux définitions de colonnes et aux données d'exemple des deux tables — category et item.

① Exécute PRAGMA table_info(category); et PRAGMA table_info(item); pour vérifier les définitions de colonnes.

② Prévisualise toutes les lignes avec SELECT * FROM category; et SELECT * FROM item;.

③ Exécute PRAGMA foreign_keys; pour voir l'état actuel de la vérification de clé étrangère (renvoyé comme un nombre).

La vérification de clé étrangère s'active avec PRAGMA foreign_keys=ON

Les contraintes de clé étrangère se déclarent en écrivant REFERENCES table_parente(col_parente) dans la définition de colonne de la table enfant.

item.cat_id porte REFERENCES category(cat_id), qui encode la règle : chaque valeur item.cat_id doit exister dans category.

Cependant, dans la console de ce cours la vérification de clé étrangère est par défaut désactivée (PRAGMA foreign_keys vaut 0).

Tant qu'elle est désactivée, `REFERENCES` n'est pas réellement appliqué et des lignes pointant vers une catégorie inexistante peuvent être insérées.

Pour appliquer réellement la contrainte, exécute PRAGMA foreign_keys=ON; une fois par connexion.

-- Activer la vérification de clé étrangère
PRAGMA foreign_keys=ON;

-- Confirmer l'état (1 signifie activé)
PRAGMA foreign_keys;

-- Tenter d'insérer une ligne pointant vers cat_id=99 (absent de category)
-- Avec foreign_keys=ON, c'est rejeté avec FOREIGN KEY constraint failed
INSERT INTO item VALUES (5,'Mouse',99);

Imagine le besoin : « faire que la base bloque les erreurs d'enregistrement où un item référence une catégorie inexistante. » Avec la vérification de clé étrangère activée, tu vas délibérément insérer une ligne incohérente et la voir échouer en erreur. (Exécute correctement et l'explication apparaît.)

① Sur la ligne 1, exécute PRAGMA foreign_keys=ON; pour activer la vérification de clé étrangère.

② Puis INSERT une ligne dans item avec item_id 5, item_name Mouse, et cat_id 99 — une valeur qui n'existe pas dans category. Cet INSERT viole la contrainte de clé étrangère, donc une erreur est le résultat correct.

ON DELETE — que faire des lignes enfants quand une ligne parente est supprimée

Quand une ligne parente (category) est supprimée, qu'arrive-t-il aux lignes enfants (item) qui pointaient vers elle ? Tu décides en écrivant une action référentielle dans la clause REFERENCES.

Il existe trois actions référentielles courantes.

ON DELETE CASCADE signifie supprimer le parent supprime aussi les enfants (suppression en cascade), ON DELETE SET NULL signifie la colonne de référence de l'enfant est mise à NULL et la ligne est conservée, et ON DELETE RESTRICT signifie la suppression du parent elle-même est refusée tant que les enfants y font encore référence.

Si tu ne spécifies pas d'action, le comportement par défaut est aussi de type RESTRICT (un parent référencé ne peut pas être supprimé).

Le comportement approprié dépend du besoin métier.

-- Exemple de déclaration avec ON DELETE SET NULL
CREATE TABLE cat_demo(cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL);
CREATE TABLE item_demo(
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  cat_id INTEGER REFERENCES cat_demo(cat_id) ON DELETE SET NULL
);
INSERT INTO cat_demo VALUES (1,'Stationery'),(2,'Electronics');
INSERT INTO item_demo VALUES (1,'Pen',1),(2,'Phone',2),(3,'Cable',2);

-- Supprimer le parent cat_id=2 met cat_id de Phone / Cable à NULL
DELETE FROM cat_demo WHERE cat_id=2;
SELECT item_id, item_name, cat_id FROM item_demo ORDER BY item_id;

Imagine le besoin : « quand une catégorie est supprimée, supprimer aussi chaque item qui lui appartient. » Construis un montage parent/enfant autonome avec ON DELETE CASCADE et observe la suppression du parent se propager en cascade vers les enfants.

① Active la vérification de clé étrangère, puis DROP TABLE IF EXISTS et recrée cat_x (cat_id, cat_name) et item_x (item_id, item_name, cat_id) où item_x.cat_id a une clé étrangère ON DELETE CASCADE vers cat_x.

② Insère 2 lignes dans cat_x (Stationery / Electronics) et 3 lignes dans item_x (une avec cat_id 1, deux avec cat_id 2).

③ Supprime la catégorie de cat_id 2 de cat_x, puis SELECT toutes les lignes de item_x et confirme que les items pointant vers cat_id=2 ont été supprimés en cascade.

SET NULL et RESTRICT — choisir entre conserver et refuser

ON DELETE SET NULL conserve la ligne enfant quand le parent est supprimé, en remplaçant simplement la colonne de référence par NULL.

Utilise-le quand tu ne veux pas supprimer l'enregistrement enfant lui-même, comme « garder l'item comme 'non classé' ».

Si la colonne de référence de l'enfant est NOT NULL, NULL ne peut pas y être écrit, donc les colonnes utilisées avec SET NULL doivent autoriser NULL.

ON DELETE RESTRICT refuse la suppression du parent elle-même tant que les enfants y font encore référence.

Utilise-le quand tu veux un comportement sûr en cas d'échec (ne pas accidentellement supprimer une ligne maître encore référencée).

Une fois que tu as supprimé ou réaffecté tous les enfants pour qu'aucune référence ne subsiste, le parent peut être supprimé.

Dans le prochain exercice, tu mettras en place SET NULL et RESTRICT côte à côte dans une console pour les comparer.

-- Exemple RESTRICT : un parent avec enfants ne peut pas être supprimé
CREATE TABLE cat_r(cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL);
CREATE TABLE item_r(
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  cat_id INTEGER REFERENCES cat_r(cat_id) ON DELETE RESTRICT
);
INSERT INTO cat_r VALUES (1,'Stationery'),(2,'Electronics');
INSERT INTO item_r VALUES (1,'Pen',1);

-- cat_id=1 est référencé par item_r, donc la suppression est refusée
DELETE FROM cat_r WHERE cat_id=1;

-- cat_id=2 n'a pas de références, donc il peut être supprimé
DELETE FROM cat_r WHERE cat_id=2;
SELECT * FROM cat_r ORDER BY cat_id;

Imagine le besoin : « si on abandonne une catégorie, garder ses items comme 'non classés' mais ne pas toucher du tout aux items des autres catégories. » Avec 3 catégories et 5 items sous ON DELETE SET NULL, tu verras que seuls les enfants du parent abandonné deviennent NULL tandis que les autres ne sont pas touchés.

① Active la vérification de clé étrangère, puis DROP TABLE IF EXISTS et recrée cat_s (cat_id, cat_name) et item_s (item_id, item_name, cat_id) où item_s.cat_id a une clé étrangère ON DELETE SET NULL vers cat_s. item_s.cat_id doit autoriser NULL.

② Insère 3 lignes dans cat_s (Office=1 / Gadget=2 / Drink=3) et 5 lignes dans item_s (Marker et Tape dans Office, Tablet et Charger dans Gadget, Coffee dans Drink).

③ Supprime juste Gadget (cat_id=2) de cat_s, puis SELECT toutes les lignes de item_s et confirme que seuls Tablet et Charger ont vu leur cat_id devenir NULL tandis que les items sous Office / Drink gardent leur cat_id d'origine — les 5 lignes sont toujours là.

Imagine le besoin : « empêcher la suppression accidentelle d'une catégorie qui a encore beaucoup d'items lui appartenant. » Construis un montage parent/enfant autonome avec ON DELETE RESTRICT et observe-le échouer en erreur quand tu tentes de supprimer un parent qui a 3 enfants.

① Active la vérification de clé étrangère, puis DROP TABLE IF EXISTS et recrée cat_t (cat_id, cat_name) et item_t (item_id, item_name, cat_id) où item_t.cat_id a une clé étrangère ON DELETE RESTRICT vers cat_t.

② Insère Stationery (cat_id 1) dans cat_t, et 3 items en dessous (par ex. Pen / Pencil / Notebook) dans item_t.

③ Tente de DELETE la catégorie de cat_id 1 de cat_t. Comme 3 enfants y font encore référence, une erreur est le résultat correct.