Contraintes déclaratives dans PostgreSQL : CHECK, UNIQUE et clés étrangères avec ON DELETE

Ce que c'est

La documentation des contraintes de PostgreSQL décrit CHECK (une expression booléenne évaluée par ligne, satisfaite lorsqu'elle vaut vrai ou null), NOT NULL, UNIQUE (qui traite les valeurs NULL comme distinctes sauf si NULLS NOT DISTINCT est écrit), PRIMARY KEY, EXCLUDE et FOREIGN KEY. L'action référentielle d'une clé étrangère indique ce qui se passe lorsque la ligne référencée est supprimée : la valeur par défaut NO ACTION lève une erreur si des lignes référençantes existent encore au moment où la contrainte est vérifiée, ce qui peut être différé à plus tard dans la transaction ; RESTRICT empêche la suppression et ne peut pas être différé ; CASCADE supprime les lignes référençantes ; SET NULL et SET DEFAULT réécrivent les colonnes référençantes. La documentation précise que déclarer une clé étrangère ne crée pas automatiquement d'index sur les colonnes référençantes.

Pourquoi c'est important

Les contraintes constituent la seule validation qui s'applique à tout auteur d'écriture : l'application, une session psql, une migration, un import en masse. Un CHECK garantissant qu'une quantité est positive, ou un UNIQUE sur une adresse électronique, élimine d'un coup toute une classe de bugs de chaque chemin de code, et l'erreur survient au niveau de l'instruction qui l'a causée, plutôt que dans un rapport des semaines plus tard.

Comment l'appliquer

• Rendre chaque colonne NOT NULL, sauf si « inconnu » est une signification que l'application traite explicitement ; ajouter des contraintes CHECK pour les règles qui ne changent jamais (price >= 0, starts_at < ends_at).
• Décider de ON DELETE relation par relation : CASCADE pour les enfants possédés (les lignes d'une commande), RESTRICT ou NO ACTION pour les références qu'une personne doit résoudre (un produit encore référencé par des commandes), SET NULL pour les liens optionnels.
• Indexer la colonne référençante de toute clé étrangère utilisée dans des jointures ou dont les lignes parentes peuvent être supprimées ; à défaut, la suppression d'un parent entraîne un parcours complet de la table enfant.
• Nommer les contraintes (CONSTRAINT orders_total_nonneg CHECK (...)) afin que les messages d'erreur et les migrations restent lisibles.
• Ajouter les contraintes de clé étrangère, CHECK et non-null à de grandes tables en tant que NOT VALID (la documentation d'ALTER TABLE n'autorise cette option que pour ces types de contraintes) puis exécuter VALIDATE CONSTRAINT par la suite, de sorte que les lignes existantes soient vérifiées sans conserver le verrou le plus fort pendant tout le parcours.
• N'utiliser DEFERRABLE INITIALLY DEFERRED que pour les contraintes devant être temporairement violées à l'intérieur d'une transaction, comme les références cycliques.

Pièges

Un CHECK ne peut pas examiner d'autres lignes ou d'autres tables ; utiliser pour cela des contraintes EXCLUDE ou des déclencheurs. Un CASCADE placé sur la mauvaise relation supprime bien plus que prévu, et le fait silencieusement. Une contrainte UNIQUE sur une colonne autorisant NULL permet plusieurs valeurs NULL, sauf si NULLS NOT DISTINCT est spécifié. Des contraintes vérifiées uniquement dans le code de l'application sont contournées par tout autre client.