Clés étrangères et intégrité référentielle

Garantissez la cohérence entre vos tables avec REFERENCES et choisissez délibérément le comportement ON DELETE.

8 min · 3 questions

Une clé étrangère déclare qu'une colonne pointe vers la clé primaire d'une autre table. La base refuse alors toute valeur orpheline : impossible de créer un livre dont l'auteur n'existe pas. Attention, en SQLite cette vérification est désactivée par défaut : activez-la avec un PRAGMA en tête de script.

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL
    REFERENCES authors(id)
    ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES
  (1, 'Ursula K. Le Guin'),
  (2, 'Ted Chiang');

INSERT INTO books (id, title, author_id) VALUES
  (1, 'Terremer', 1),
  (2, 'Les Depossedes', 1),
  (3, 'Exhalaison', 2);

Testez la protection : insérez un livre orphelin, puis supprimez un auteur. La première instruction est refusée ; la seconde entraîne ses livres grâce à ON DELETE CASCADE.

INSERT INTO books (id, title, author_id)
VALUES (4, 'Orphelin', 99);

DELETE FROM authors WHERE id = 1;

SELECT id, title FROM books;

ON DELETE définit le sort des lignes enfants quand le parent disparaît. CASCADE les supprime avec lui. SET NULL conserve la ligne mais efface la référence - la colonne doit donc accepter NULL. Sans clause, SQLite applique NO ACTION : la suppression du parent est refusée tant qu'il reste des enfants, comportement proche de RESTRICT.

CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  reviewer_id INTEGER
    REFERENCES authors(id)
    ON DELETE SET NULL
);

Quiz de validation

Vérifiez que vous avez bien retenu les points clés de cette leçon.

1. Avec ON DELETE CASCADE sur books.author_id, que provoque DELETE FROM authors WHERE id = 1 ?
   • Une erreur tant que l'auteur a des livres
   • La suppression de l'auteur et de tous ses livres
   • La mise à NULL de author_id dans ses livres
2. Pourquoi exécuter PRAGMA foreign_keys = ON; en SQLite ?
   • Pour créer les clés étrangères
   • Parce que la vérification des clés étrangères est désactivée par défaut, connexion par connexion
   • Pour accélérer les jointures
3. Quelle condition la colonne reviewer_id doit-elle remplir pour utiliser ON DELETE SET NULL ?
   • Être déclarée UNIQUE
   • Être de type INTEGER
   • Accepter NULL, donc ne pas porter NOT NULL