Contraintes CHECK PostgreSQL : règles locales à la ligne et pourquoi les comparaisons entre lignes cassent les sauvegardes
Les contraintes CHECK PostgreSQL ne peuvent référencer que la ligne insérée ou mise à jour. Un CHECK comparant d'autres lignes peut réussir des tests simples mais échouer lors de la restauration pg_dump, car les lignes se chargent dans un ordre qui peut ne pas le satisfaire.
Dans ce guide
Idée principale
Dans PostgreSQL, une contrainte CHECK est une expression booléenne évaluée uniquement sur la ligne nouvelle ou mise à jour. Elle peut référencer les colonnes de cette ligne, des constantes, des fonctions immuables et des opérateurs, mais elle ne doit pas référencer d'autres lignes ni d'autres tables. PostgreSQL n'applique pas cette restriction au moment de la définition, donc un CHECK inter-lignes peut être créé et sembler fonctionner dans de petits tests. Il ne peut toutefois pas garantir l'invariant, car des modifications ultérieures de la ligne référencée peuvent fausser la condition sans revérifier la ligne d'origine. La conséquence documentée est qu'une sauvegarde et une restauration de base de données peuvent échouer : les lignes sont rechargées dans un ordre qui peut ne pas satisfaire la contrainte, même lorsque l'état final de la base est cohérent. Pour les règles inter-lignes ou inter-tables, utilisez des contraintes UNIQUE, EXCLUDE ou FOREIGN KEY, ou appliquez la règle dans la logique applicative ou un déclencheur. Rappelez-vous aussi qu'un CHECK réussit lorsque l'expression vaut NULL, donc associez-le à NOT NULL lorsque les valeurs nulles doivent être exclues.
Contraintes CHECK autorisées
Une contrainte CHECK dans PostgreSQL est le type de contrainte le plus générique. Elle attache une expression booléenne à une colonne ou à la table, et l'expression est évaluée chaque fois qu'une ligne est insérée ou mise à jour. L'expression doit impliquer la colonne contrainte, sinon elle sert peu. Les contraintes de colonne et de table sont interchangeables dans de nombreux cas, et une contrainte de table peut référencer plusieurs colonnes de la même ligne.
L'expression peut utiliser les colonnes de la ligne vérifiée, des constantes littérales, des opérateurs et des fonctions. Elle ne doit pas référencer de données de table autres que la ligne nouvelle ou mise à jour. Il s'agit d'une restriction documentée, non d'une préférence stylistique. La contrainte est vérifiée sur la ligne candidate isolément, elle n'a donc pas accès aux autres lignes au moment de l'évaluation.
Une subtilité qui surprend beaucoup de praticiens est la gestion des valeurs nulles. Une contrainte CHECK est satisfaite lorsque l'expression vaut vrai ou la valeur nulle. Comme la plupart des expressions renvoient null dès qu'un opérande est null, un CHECK n'empêche pas à lui seul les valeurs nulles dans les colonnes contraintes. Pour interdire les nulls, ajoutez une contrainte NOT NULL, fonctionnellement équivalente à CHECK (column IS NOT NULL) mais plus efficace dans PostgreSQL.
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric,
CHECK (price > discounted_price)
);Le risque des références entre lignes
PostgreSQL ne prend pas en charge les contraintes CHECK qui référencent des données de table autres que la ligne nouvelle ou mise à jour vérifiée. La documentation précise explicitement qu'un CHECK violant cette règle peut sembler fonctionner dans des tests simples, mais qu'il ne peut pas garantir que la base n'atteindra pas un état où la condition de contrainte est fausse. La raison est que la condition dépend d'autres lignes, et ces lignes peuvent changer après la validation de la ligne d'origine. Rien ne revérifie la ligne d'origine lorsque la ligne référencée est modifiée.
Il s'agit d'un problème de correction, pas seulement de performance. Une contrainte qui ne tient qu'au moment de l'insertion donne une fausse impression d'intégrité. La base peut dériver vers un état où l'invariant est violé, sans qu'aucune erreur ne soit levée au moment de la dérive. La défaillance se manifeste plus tard, souvent au pire moment possible.
Le même raisonnement s'applique aux fonctions utilisées dans un CHECK. Une fonction qui lit d'autres tables introduit la même dépendance inter-lignes, même si la syntaxe paraît locale. La restriction porte sur ce que l'expression peut observer, pas sur la manière dont elle est écrite.
Échecs de sauvegarde et de restauration
La conséquence documentée d'un CHECK inter-lignes est qu'une sauvegarde et une restauration de base de données peuvent échouer. Lors de la restauration, les lignes sont chargées dans un ordre déterminé par la sauvegarde, et cet ordre peut ne pas satisfaire la contrainte à chaque étape intermédiaire. La restauration peut échouer même lorsque l'état complet de la base est cohérent avec la contrainte, car la contrainte est évaluée ligne par ligne au fur et à mesure de l'insertion des données.
Cela rend le problème opérationnellement sérieux. Une sauvegarde qui se restaure proprement en développement peut échouer en production si l'ordre des lignes diffère, si le volume de données modifie l'ordre de chargement, ou si la sauvegarde a été prise à un autre moment du cycle de vie des données. L'échec n'est pas déterministe par rapport à l'état final ; il dépend du chemin emprunté pour l'atteindre.
La leçon pratique est qu'une contrainte qui ne peut pas être évaluée à partir d'une seule ligne ne peut pas être considérée comme une garantie déclarative. Elle peut passer les tests, et même réussir une restauration une fois, mais elle n'offre pas la propriété d'intégrité qu'elle semble offrir.
Alternatives recommandées
Lorsqu'une règle couvre réellement plusieurs lignes ou tables, PostgreSQL propose des contraintes déclaratives conçues à cet effet. Utilisez UNIQUE pour l'unicité entre lignes, EXCLUDE pour les règles de plage et de chevauchement, et FOREIGN KEY pour l'intégrité référentielle. Ces contraintes sont appliquées par la base sur les lignes concernées et sont maintenues correctement lorsque les données changent.
Pour les règles qu'aucune de ces contraintes n'exprime, utilisez un déclencheur ou une validation au niveau applicatif, et documentez clairement que la règle n'est pas une contrainte déclarative. Un déclencheur peut observer d'autres lignes et peut être écrit pour revalider lors des changements pertinents, mais il comporte sa propre complexité et doit être maintenu avec soin.
Un modèle mental utile consiste à se demander si la règle peut être décidée à partir de la seule ligne candidate. Si oui, un CHECK est approprié et peu coûteux. Si non, la règle appartient à un type de contrainte qui comprend la relation, ou à du code procédural que vous acceptez comme point d'application.
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text NOT NULL,
price numeric NOT NULL CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CONSTRAINT valid_discount CHECK (price > discounted_price)
);Conditions d’application
- L'expression CHECK référence-t-elle uniquement des colonnes de la ligne insérée ou mise à jour ?
- La règle implique-t-elle d'autres lignes ou tables, ce qui nécessiterait plutôt UNIQUE, EXCLUDE ou FOREIGN KEY ?
- Les colonnes nullables sont-elles associées à NOT NULL là où les valeurs nulles doivent être exclues ?
- Avez-vous testé une sauvegarde et une restauration de la table pour confirmer que la contrainte survit au rechargement ?
- La contrainte est-elle nommée pour pouvoir être identifiée et modifiée ultérieurement ?
Champ d’application
Cet article décrit le comportement de PostgreSQL tel que documenté pour les versions prises en charge (14 à 18 au moment de la rédaction). La formulation exacte des messages d'erreur et le comportement de sauvegarde et de restauration peuvent varier selon la version et les outils utilisés (pg_dump, pg_restore ou réplication logique). Les exemples sont illustratifs et supposent une installation par défaut sans déclencheurs de contrainte personnalisés. L'article ne couvre pas en détail les contraintes différées, les opérateurs de contraintes d'exclusion ni l'application par déclencheurs ; ceux-ci nécessitent un traitement séparé. Il n'affirme pas non plus qu'une restauration particulière échouera, seulement qu'un CHECK inter-lignes ne peut pas garantir l'intégrité et peut provoquer l'échec d'une restauration selon l'ordre de chargement des lignes.