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.
RESTRICTvérifie immédiatement,NO ACTIONen 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_idd'une commande).- Index sur la clé étrangère
PostgreSQL crée un index pour
PRIMARY KEYetUNIQUE, mais pas pour une clé étrangère. Sans cet index, chaque suppression du parent parcourt entièrement la table enfant.
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.