Formations — 1 037 leçons en français

Accueil › SQL & bases de données › Clés étrangères et intégrité référentielle

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

Les jointuresdebutant

Une clé étrangère déclare qu'une colonne pointe vers la clé primaire d'une autre table, et charge la base de le vérifier.

CREATE TABLE commande (
    id        integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    client_id integer NOT NULL REFERENCES client(id),
    total     numeric(10, 2) NOT NULL
);

INSERT INTO commande (client_id, total) VALUES (999, 50.00);
-- ERROR:  insert or update on table "commande" violates foreign key constraint

Aucune commande ne peut plus désigner un client inexistant — ni par bug applicatif, ni par script d'import, ni par correction manuelle.

Reste à décider ce qui arrive aux enfants quand le parent disparaît :

Clause Effet
(défaut) NO ACTION suppression du parent refusée
ON DELETE CASCADE les enfants sont supprimés aussi
ON DELETE SET NULL la référence passe à NULL

Un détail coûte très cher quand on l'ignore : PostgreSQL n'indexe pas automatiquement les clés étrangères. L'index existe côté parent (c'est la clé primaire), pas côté enfant. Sans CREATE INDEX ON commande (client_id), chaque suppression de client parcourt toute la table des commandes.

Les termes à connaître

Clé étrangère (FOREIGN KEY)

Une colonne qui ne peut contenir qu'une valeur existant dans la clé primaire d'une autre table — ou NULL. C'est la base qui le vérifie, à chaque insertion et à chaque modification.

Intégrité référentielle

La garantie qu'aucune ligne ne référence une ligne inexistante : pas de commande rattachée à un client supprimé. Sans contrainte, ces « orphelins » apparaissent tôt ou tard.

ON DELETE RESTRICT / NO ACTION

Comportement par défaut : la suppression du parent est refusée s'il reste des enfants. RESTRICT vérifie immédiatement, NO ACTION en fin d'instruction (ce qui autorise une correction dans la même instruction).

ON DELETE CASCADE

Supprimer le parent supprime automatiquement les enfants. Puissant et dangereux : une seule ligne effacée peut en emporter des millions, en cascade sur plusieurs niveaux.

ON DELETE SET NULL

Le parent supprimé, la colonne de référence passe à NULL. Suppose évidemment que la colonne soit nullable — utile pour un lien optionnel (vendeur_id d'une commande).

Index sur la clé étrangère

PostgreSQL crée un index pour PRIMARY KEY et UNIQUE, mais pas pour une clé étrangère. Sans cet index, chaque suppression du parent parcourt entièrement la table enfant.

Lire l'explication détaillée et faire le quiz →

Cette page présente la partie essentielle de la leçon. Les exemples commentés, les pièges courants et les trois questions de quiz sont dans l'application.