PostgreSQL : choisir ON DELETE RESTRICT, CASCADE ou SET NULL sans perdre les enregistrements liés

Cadrer la politique de suppression comme une décision de rétention des données

Chaque clé étrangère porte une réponse implicite à une question métier : quand la ligne référencée disparaît, que doit-il arriver aux lignes qui pointent vers elle ? ON DELETE RESTRICT répond que les dépendants doivent survivre et que le parent ne peut pas être retiré tant que ces dépendants ne sont pas gérés explicitement. ON DELETE CASCADE répond que les dépendants sont des parties inséparables du parent et doivent disparaître ensemble. ON DELETE SET NULL répond que la référence est optionnelle et que la ligne dépendante peut continuer à exister sans elle. Ce ne sont pas des options syntaxiques interchangeables ; elles encodent des règles de rétention qui affectent quelles données restent interrogeables après une suppression.

La documentation PostgreSQL indique que les actions reflètent des choix intuitifs : interdire la suppression d'un produit référencé, supprimer les commandes également, ou autre chose. Choisir la mauvaise option peut silencieusement retirer des lignes qu'une autre relation aurait besoin de préserver, ou bloquer un nettoyage légitime parce que les dépendants n'étaient jamais destinés à survivre.

Cette décision doit suivre le sens de la relation, et non la commodité de l'auteur du schéma. Une entrée de catalogue produits référencée par des commandes historiques n'est pas la même chose qu'une ligne d'article de commande qui n'existe que parce que la commande existe.

CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric);
CREATE TABLE orders (order_id integer PRIMARY KEY, shipping_address text);
CREATE TABLE order_items (
  product_no integer REFERENCES products ON DELETE RESTRICT,
  order_id integer REFERENCES orders ON DELETE CASCADE,
  quantity integer,
  PRIMARY KEY (product_no, order_id)
);

Lire le comportement par défaut NO ACTION avant de le changer

Si vous écrivez une clé étrangère sans clause ON DELETE, PostgreSQL applique ON DELETE NO ACTION. La documentation explique que cela signifie que la suppression dans la table référencée est autorisée à procéder, mais que la contrainte de clé étrangère doit toujours être satisfaite, donc l'opération résultera généralement en une erreur. La distinction clé avec RESTRICT est le moment et la différabilité. NO ACTION vérifie la contrainte à la fin de l'instruction ou à la fin de la transaction si la contrainte est différable, donnant aux autres commandes une chance de corriger la situation avant que la vérification ne se déclenche. RESTRICT vérifie immédiatement et ne peut pas être différé.

Se reposer sur le défaut implicite est fragile car un lecteur du schéma ne peut pas dire si l'auteur avait l'intention d'une politique ou a simplement oublié d'en spécifier une. Rendre l'action explicite, même lorsqu'il s'agit de NO ACTION, documente la décision et supprime l'ambiguïté lors de la revue de code ou de la migration.

-- These two are NOT equivalent:
-- product_no integer REFERENCES products  -- defaults to NO ACTION, deferrable
-- product_no integer REFERENCES products ON DELETE RESTRICT  -- immediate, non-deferrable

Choisir RESTRICT lorsque les lignes référencées doivent survivre

Utilisez ON DELETE RESTRICT lorsque la suppression d'une ligne parent alors que des lignes dépendantes existent doit échouer catégoriquement. Cela force l'application ou l'opérateur à prendre une décision explicite concernant les dépendants avant que le parent puisse être retiré. La documentation PostgreSQL illustre cela avec l'exemple produits-et-articles-de-commande : les produits et les commandes sont des choses différentes, donc faire en sorte qu'une suppression de produit cause automatiquement la suppression de certains articles de commande pourrait être considérée comme problématique.

RESTRICT est le bon défaut pour les données principales, les tables de référence et toute entité dont le retrait a des conséquences en aval nécessitant une revue humaine. Le message d'erreur de RESTRICT est déterministe et immédiat, ce qui le rend facile à attraper dans le code de l'application et à présenter un message significatif à l'utilisateur.

-- Attempting this with RESTRICT on product_no will raise an error:
DELETE FROM products WHERE product_no = 7;
-- ERROR: update or delete on table "products" violates
--        foreign key constraint on table "order_items"

Choisir CASCADE uniquement pour les lignes dépendantes du cycle de vie

ON DELETE CASCADE est approprié lorsque les lignes enfants n'existent que dans le cadre du cycle de vie du parent et n'ont aucune signification indépendante. La documentation note que les articles de commande font partie d'une commande, et il est pratique qu'ils soient supprimés automatiquement si une commande est supprimée. La cascade voyage de la ligne référencée à chaque ligne référençante en une seule opération.

Le danger est que CASCADE peut retirer des données qu'une relation différente aurait besoin de préserver. Si une ligne order_item est également référencée par un manifeste d'expédition ou un journal d'audit, cascader la suppression depuis orders retirera l'order_item et violera ensuite ou cascadera plus loin le long de ces autres clés étrangères. Utilisez CASCADE uniquement lorsque vous avez tracé chaque référence en aval et confirmé que le retrait automatique est le comportement intentionnel pour toutes.

-- Deleting an order cascades to its line items:
DELETE FROM orders WHERE order_id = 42;
-- All rows in order_items with order_id = 42 are removed automatically.

Choisir SET NULL uniquement pour les références optionnelles

ON DELETE SET NULL conserve la ligne référençante mais définit la colonne de clé étrangère à NULL. Ceci est approprié lorsque la relation représente des informations optionnelles. La documentation donne l'exemple d'une référence de gestionnaire de produit : si l'entrée du gestionnaire de produit est supprimée, définir le gestionnaire de produit du produit à null pourrait être utile.

SET NULL exige que les colonnes référençantes acceptent les valeurs NULL. Il ne peut pas être utilisé sur une colonne NOT NULL ou sur une colonne faisant partie d'une clé primaire sans planification soignée. Pour les clés étrangères composites, la forme liste-de-colonnes permet de définir seulement un sous-ensemble des colonnes référençantes à NULL tout en laissant les autres intactes, ce qui est nécessaire lorsqu'une partie de la clé composite doit rester peuplée.

-- Column-list form for composite foreign keys:
CREATE TABLE posts (
  tenant_id integer REFERENCES tenants ON DELETE CASCADE,
  post_id integer NOT NULL,
  author_id integer,
  PRIMARY KEY (tenant_id, post_id),
  FOREIGN KEY (tenant_id, author_id) REFERENCES users
    ON DELETE SET NULL (author_id)
);
-- Without the column list, tenant_id would also be set to NULL,
-- violating the primary key.

Comparer les trois actions sur un petit schéma

Considérez le schéma products, orders et order_items de la documentation. La table order_items a deux clés étrangères avec des actions différentes : product_no utilise RESTRICT et order_id utilise CASCADE. Si vous tentez DELETE FROM products WHERE product_no = 7 et que ce produit apparaît dans n'importe quelle ligne order_items, l'instruction échoue immédiatement avec une erreur de violation de clé étrangère. Aucune ligne n'est retirée de l'une ou l'autre table.

Si vous tentez DELETE FROM orders WHERE order_id = 42, l'action CASCADE retire chaque ligne order_items référençant cette commande, et la suppression réussit. Le même schéma démontre à la fois un comportement protecteur et automatique selon le parent que vous ciblez.

SET NULL s'appliquerait si, par exemple, orders avait une colonne assigned_shipper optionnelle référençant une table shipper. Supprimer un expéditeur laisserait la commande intacte avec assigned_shipper défini à NULL, préservant l'historique de commande tout en retirant la référence désormais invalide.

-- Outcome table for the example schema:
-- DELETE FROM products WHERE product_no = 7
--   -> ERROR (RESTRICT blocks it, dependents survive)
-- DELETE FROM orders WHERE order_id = 42
--   -> SUCCESS (CASCADE removes matching order_items)
-- DELETE FROM shippers WHERE shipper_id = 3
--   -> SUCCESS (SET NULL clears orders.assigned_shipper)

Vérifier la politique avant de l'appliquer aux données réelles

Avant d'altérer une contrainte en production, inspectez l'action actuelle avec une requête contre pg_constraint ou en lisant le schéma avec \d dans psql. Identifiez combien de lignes dépendantes existent pour les lignes parents que vous prévoyez de supprimer. La documentation avertit que écrire DELETE FROM products sans clause WHERE retire toutes les lignes, donc scopez toujours vos suppressions de test.

Exécutez la DELETE prévue dans une transaction que vous annulez. Cela vous permet d'observer si RESTRICT soulève une erreur, si CASCADE retire le nombre attendu d'enfants, ou si SET NULL laisse des lignes avec des références NULL, le tout sans valider les changements. Seulement après avoir confirmé le comportement devriez-vous émettre un ALTER TABLE pour changer l'action de la contrainte en production.

BEGIN;
DELETE FROM orders WHERE order_id = 42;
SELECT count(*) FROM order_items WHERE order_id = 42;
-- Expect 0 if CASCADE is correct.
ROLLBACK;
-- No data was permanently removed.

Savoir quand la politique est insuffisante

Ces actions régissent ce qui arrive aux lignes référençantes lorsqu'une ligne référencée est supprimée. Elles ne gèrent pas le nettoyage arbitraire, les calendriers de rétention de données, ou le retrait de lignes qui ne sont pas connectées par une clé étrangère. Un CASCADE ne peut pas compenser un modèle de relation incorrect ; si les lignes enfants n'auraient pas dû être liées au parent en premier lieu, aucune action de suppression ne corrigera la conception sous-jacente du schéma.

La documentation note également que ON UPDATE a un comportement lié mais pas identique, et que les listes de colonnes ne peuvent pas être spécifiées pour SET NULL et SET DEFAULT sous ON UPDATE. Une politique qui fonctionne pour les suppressions peut ne pas se transférer directement aux mises à jour. Enfin, une instruction DELETE illimitée combinée avec CASCADE peut retirer de grands volumes de données dans une seule transaction, donc les garde-fous au niveau de l'application et les clauses WHERE explicites restent nécessaires indépendamment de l'action de la contrainte.

-- This single statement could cascade through many tables:
DELETE FROM products;  -- no WHERE clause, caveat programmer
-- If CASCADE is set on multiple levels, this can be destructive.