Menu

Clé étrangère PostgreSQL : REFERENCES et ON DELETE CASCADE

Une clé étrangère n'accepte dans une colonne que des valeurs qui existent dans une autre table. Voici REFERENCES, ON DELETE CASCADE, SET NULL et RESTRICT, l'ajout d'une clé à une table existante et son index.

Cette page contient des éditeurs exécutables. Modifiez, exécutez et voyez la sortie instantanément.

Une clé étrangère relie une colonne à la clé primaire, ou à une colonne unique, d'une autre table. PostgreSQL refuse toute valeur qui n'a pas de ligne correspondante là-bas. On l'écrit column type REFERENCES other_table (column). Ajoutez ON DELETE CASCADE ou ON DELETE SET NULL pour dire ce que deviennent les lignes enfants quand le parent est supprimé.

La colonne référencée doit avoir une contrainte PRIMARY KEY ou UNIQUE. Avec REFERENCES customers sans colonne, PostgreSQL prend la clé primaire de customers.

À quoi ressemble une violation

Une commande pour un client qui n'existe pas est refusée :

L'erreur nomme la contrainte, orders_customer_id_fkey, le nom que PostgreSQL donne à une clé étrangère sans nom. Supprimer un client qui a encore des commandes échoue de la même façon, sauf si la clé prévoit autre chose.

Les options de ON DELETE

OptionQuand la ligne parente est supprimée
NO ACTION (par défaut)La suppression échoue tant qu'il reste des lignes enfants
RESTRICTPareil, mais la vérification ne peut pas être reportée à la fin de la transaction
CASCADELes lignes enfants sont supprimées aussi
SET NULLLa colonne de clé étrangère de l'enfant passe à NULL
SET DEFAULTLa colonne de l'enfant prend sa valeur par défaut

ON UPDATE accepte les mêmes options. Elle s'applique quand la valeur de la clé du parent change, ce qui est rare avec des identifiants générés.

CASCADE convient aux lignes qui n'ont pas de sens sans leur parent, comme les lignes d'une commande :

Il ne reste que chair. Les cascades suivent les clés suivantes, donc une seule suppression peut retirer des lignes dans plusieurs tables.

SET NULL garde la ligne enfant et la détache :

Ada reste, avec team_id à NULL. Depuis PostgreSQL 15, ON DELETE SET NULL (column) peut ne vider que certaines colonnes d'une clé multicolonne.

Ajouter une clé étrangère à une table existante

ALTER TABLE orders
    ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;

Cette commande vérifie chaque ligne existante et échoue si l'une d'elles n'a pas de parent. Sur une grosse table, ajoutez la clé avec NOT VALID, qui ne vérifie que les nouvelles lignes. Lancez ensuite ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey; pour vérifier les anciennes lignes sans bloquer les écritures. Pour changer l'action ON DELETE d'une clé existante, supprimez la contrainte et ajoutez-la de nouveau dans un seul ALTER TABLE.

Indexer la colonne de clé étrangère

PostgreSQL indexe le côté référencé, pas la colonne qui référence. Sans index sur orders.customer_id, chaque suppression dans customers parcourt tout orders pour chercher des enfants, et c'est pareil pour récupérer les commandes d'un client :

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

Pour lister les clés étrangères d'une table, interrogez pg_constraint :

Les jointures sur ces clés sont expliquées sur la page des jointures, et le chapitre sur les contraintes du manuel donne toutes les règles, y compris les clés différables.

Questions fréquentes

PostgreSQL crée-t-il un index pour une clé étrangère ?

Pas sur la colonne qui référence. La colonne référencée en a déjà un, puisqu'elle doit être une clé primaire ou unique. Créez vous-même un index sur la colonne de la clé étrangère, car les jointures et les suppressions dans la table parente la parcourent.

Comment supprimer une clé étrangère en PostgreSQL ?

ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;. Une clé étrangère sans nom s'appelle table_column_fkey ; \d orders dans psql affiche le nom exact.

Une colonne de clé étrangère peut-elle être NULL en PostgreSQL ?

Oui. Un NULL dans la colonne de la clé étrangère n'est pas vérifié, donc la ligne n'a pas besoin de parent. Ajoutez NOT NULL quand chaque ligne doit en avoir un.

Une clé étrangère peut-elle référencer la même table ?

Oui. Une colonne comme manager_id int REFERENCES employees (id) pointe vers une autre ligne de la même table. C'est ainsi qu'on stocke des arbres, comme un organigramme ou un fil de commentaires.

Illustration des langages de programmation de Coddy

Apprendre à coder avec Coddy

Commencer