Modèle relationnel, types, NULL et contraintes

Comprendre le mécanisme

Une clé primaire identifie une ligne et interdit null ; une clé étrangère exprime une relation vers une clé admissible. UNIQUE protège une unicité, tandis que CHECK contrôle une expression de ligne. Attention : une expression CHECK qui vaut inconnu à cause de null ne rejette pas nécessairement la ligne ; NOT NULL exprime explicitement l’obligation de valeur. Une règle multi-lignes demande un mécanisme approprié plutôt qu’un CHECK supposant un état global stable.

Raisonner et vérifier

Choisissez les types selon le domaine : numeric pour une précision décimale définie, entiers pour des quantités discrètes, timestamptz pour représenter un instant avec conversion d’affichage. Les noms et contraintes documentent le contrat partagé par toutes les applications. Testez aussi les écritures SQL directes : une validation dans une seule API ne protège pas tous les producteurs de données.

Identifier une ligne sans lui donner des droits

Une clé primaire impose une identité de ligne selon son contrat de non-nullité et d’unicité. Elle fournit une référence utile aux relations et aux recherches, mais ne définit pas qui peut consulter cette ligne. Un identifiant reçu doit encore être validé et résolu dans le périmètre métier autorisé. La présence d’une clé et une authentification réussie ne suffisent pas à protéger un objet tiers.

Déclarer la relation entre tables

Une clé étrangère protège une relation, par exemple une réservation qui doit viser un produit existant. Sans cette contrainte, un contrôle dans un formulaire peut être contourné par un autre client SQL ou devenir incorrect sous concurrence. Choisissez les colonnes et la politique de modification de la référence. Le nom product_id ne crée aucune garantie à lui seul : c’est la contrainte déclarée qui participe au contrat de stockage.

Présence et prédicat sont deux exigences

CHECK (quantity > 0) ne remplace pas NOT NULL. Une comparaison avec une valeur nulle peut produire un résultat inconnu, que ce CHECK ne rejette pas comme faux. Si la quantité est obligatoire et positive, exprimez les deux conditions. Les tests doivent inclure null, zéro, un négatif et un positif ; chacun vérifie une distinction du contrat plutôt qu’une simple variante du cas nominal.

L’unicité doit survivre à la concurrence

Une lecture préalable qui constate l’absence d’une référence ne réserve pas cette absence pour les autres transactions. Deux acteurs peuvent donc essayer la même insertion. Une contrainte UNIQUE donne au moteur une règle à appliquer au moment pertinent. Définissez ensuite la traduction de son refus en résultat métier. Un test utile synchronise deux connexions pour observer cette course au lieu d’enchaîner deux demandes séquentielles.

Choisir le sens des valeurs nulles

Une règle d’unicité doit préciser le traitement des valeurs nulles. Ne supposez pas que toutes les bases et toutes les options les considèrent toujours comme une seule valeur ordinaire. Vérifiez la sémantique choisie par PostgreSQL et les possibilités de la version ciblée. Si le domaine interdit l’absence, ajoutez sa contrainte explicite plutôt que tenter de déduire cette obligation de l’unicité seule.

La suppression référentielle est une décision métier

Une action CASCADE peut propager la suppression vers des lignes dépendantes. Cette commodité technique doit correspondre au cycle de vie voulu : supprimer un produit ne signifie pas nécessairement effacer un historique de facturation. Dessinez les dépendances avant de choisir l’action. Testez la suppression du parent, les références encore actives et la politique de conservation ; le moteur applique la règle déclarée, pas une intention implicite.

Vérifier la règle au niveau du stockage

Une contrainte de base protège les écritures de tous les clients soumis à cette règle. Un contrôle d’interface seul ne fournit pas cette propriété. Dans le laboratoire, tentez une écriture directe invalide avec le rôle approprié et observez le refus. Pour JPA, une écriture peut être différée : un test transactionnel doit provoquer le flush nécessaire afin que la base vérifie effectivement la contrainte avant le rollback de test.

Choisir une représentation fidèle au domaine

Une colonne text ne valide pas automatiquement une date ou un montant. Pour une valeur décimale exacte, un numeric avec précision et échelle adaptées peut être pertinent ; ses bornes et arrondis doivent correspondre au produit. L’objectif n’est pas de choisir le type le plus permissif, mais de faire participer le stockage au contrat. Définissez aussi les conversions entre API, application et base.

Migrer des données déjà présentes

Ajouter une contrainte à une base existante peut échouer si des lignes violent la nouvelle règle. Inventoriez ces états avant la migration et définissez leur traitement. Le déploiement doit considérer les applications encore actives qui écrivent selon l’ancien contrat. Une migration réussie sur une base vide ne prouve pas son comportement sur les données réelles ; préparez des fixtures représentatives et une procédure de reprise.

Normaliser selon les invariants utiles

Dupliquer une valeur dans plusieurs tables peut améliorer certains usages, mais crée un besoin de cohérence entre copies. Une normalisation adaptée explicite les responsabilités et relations importantes. Si une copie historique est voulue, documentez qu’elle représente une valeur à un instant et non une référence toujours synchronisée. Le schéma doit aider à lire le contrat métier, sans transformer une préférence de structure en garantie universelle de performance.

Atelier guidé : lire, expliquer et vérifier le code

CREATE TABLE lab_payments (
    id bigint PRIMARY KEY,
    amount integer CHECK (amount > 0)
);
INSERT INTO lab_payments(id, amount) VALUES (1, NULL);

La table de l’exercice possède un CHECK qui demande un montant positif. Pour la ligne dont amount vaut NULL, le prédicat n’est pas vrai ou faux comme pour un entier ordinaire : il produit une valeur inconnue. Ce seul CHECK ne fournit donc pas la présence obligatoire. La clé primaire protège id, pas amount. Il faut ajouter NOT NULL si l’absence doit être refusée.

Dans une base de laboratoire, testez séparément NULL, zéro, une valeur négative et une valeur positive. Notez quelle règle refuse chaque entrée. Rejouez l’écriture depuis un client SQL direct pour vérifier que la garantie ne dépend pas seulement du formulaire. Si vous utilisez un ORM qui diffère l’écriture, déclenchez le flush au point prévu afin que le test observe bien la décision du moteur.

JPA : persist, flush et commit ne sont pas équivalents

Un ORM peut différer l’exécution SQL. persist enregistre une intention dans le contexte de persistance sans que toutes les contraintes de la base aient nécessairement été évaluées immédiatement. Un test transactionnel annulé automatiquement peut donc réussir avant que l’écriture fautive soit réellement envoyée.

Pour une contrainte UNIQUE immédiate du schéma réel, déclenchez flush dans le test et vérifiez l’exception. Ce contrôle force la synchronisation SQL pertinente. Il ne démontre pas qu’un commit a réussi : contraintes différées, événements après commit et visibilité inter-transaction demandent des cas séparés.

Utilisez une base isolée avec le schéma et les contraintes attendus, plutôt qu’un mock qui accepte toute ligne. Le test doit choisir la frontière qu’il veut établir et éviter de confondre rollback du test avec rollback du service. Documentez les contraintes différables et vérifiez au commit celles dont le calendrier l’exige.

Points de vigilance : Un mock ne prouve pas le comportement de la contrainte de la base. Supprimer la contrainte retire précisément le comportement à vérifier. L’écoulement du temps ne constitue pas un mécanisme de flush fiable. Le flush force l’exécution SQL et expose la contrainte. Un flush réussi ne prouve toutefois pas qu’un commit a eu lieu.