Écrire des contraintes CHECK conformes à la règle métier

Une contrainte appelée PositiveQuantity semble garantir une quantité positive dans chaque ligne. Si son expression est Quantity > 0 et que la colonne accepte NULL, la promesse est incomplète. SQL Server rejette FALSE, tandis que UNKNOWN dû à NULL peut passer. Le nom ne modifie pas cette sémantique.

Décrire les états autorisés

La première table montre cette lacune : l'insertion de NULL réussit. Si la quantité est obligatoire, combinez NOT NULL et le contrôle positif. Si une quantité inconnue est légitime, documentez ce sens au lieu de présenter le CHECK comme une obligation de nombre.

La seconde table décrit une publication. Un brouillon ne doit pas avoir de date de publication ; une ligne publiée doit en avoir une. Status est obligatoire et limité à deux valeurs.

CREATE TABLE #WeakRule
(
    ItemId int PRIMARY KEY,
    Quantity int NULL CHECK (Quantity > 0)
);
INSERT #WeakRule VALUES (1, NULL);
SELECT ItemId, Quantity FROM #WeakRule;

CREATE TABLE #PublicationRule
(
    ItemId int PRIMARY KEY,
    Status varchar(10) NOT NULL
        CHECK (Status IN ('Draft', 'Published')),
    PublishedAt datetime2(0) NULL,
    CHECK
    (
        (Status = 'Draft' AND PublishedAt IS NULL)
        OR
        (Status = 'Published' AND PublishedAt IS NOT NULL)
    )
);
INSERT #PublicationRule VALUES
(1, 'Draft', NULL),
(2, 'Published', '20250115T12:00:00');

BEGIN TRY
    INSERT #PublicationRule VALUES (3, 'Published', NULL);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ConstraintError;
END CATCH;
SELECT * FROM #PublicationRule ORDER BY ItemId;
DROP TABLE #PublicationRule;
DROP TABLE #WeakRule;

La règle faible retourne sa ligne NULL. La table de publication accepte les deux premières lignes et rejette la troisième. Les tests explicites IS NULL et IS NOT NULL évitent un UNKNOWN accidentel dans la relation entre colonnes.

Avant une condition complexe, écrivez une petite table de vérité : Draft avec et sans date, Published avec et sans date, statut inconnu et statut NULL. Transformez ces cas en contrôles de migration ou d'intégration. Tester seulement les insertions valides laisse l'essentiel sans vérification.

Gardez les expressions lisibles. Une chaîne de négations peut être correcte mais difficile à faire évoluer. Séparez domaines indépendants et relations entre colonnes. Nommez clairement les contraintes persistantes pour faciliter le diagnostic des erreurs.

Respecter la limite d'une règle de ligne

CHECK convient aux invariants de la ligne, comme une plage autorisée ou une fin postérieure au début. Ce n'est pas un ordonnanceur qui réévalue les anciennes lignes au fil du temps. Une expression utilisant l'heure actuelle est contrôlée lors des écritures concernées, pas continuellement à minuit.

Ne cachez pas des règles de stock global ou de nombre minimal de lignes dans une fonction scalaire appelée par CHECK. D'autres lignes peuvent changer sans déclencher la réévaluation de la ligne d'origine. Les suppressions posent aussi problème. Utilisez des contraintes relationnelles appropriées ou une transaction qui protège réellement l'invariant partagé.

Une clé étrangère exprime une appartenance à une autre table plus directement qu'une fonction de recherche. Une contrainte unique protège l'unicité mieux qu'un comptage des doublons apparents. Une règle 'une seule affectation courante' peut demander un index unique filtré, pas seulement des dates valides sur chaque ligne.

L'autorisation reste distincte. Une ligne structurellement correcte peut appartenir à un tenant interdit à l'appelant. Le contrat d'écriture doit donc vérifier droits et structure séparément.

Déployer sans masquer les anciennes anomalies

Ajouter une contrainte avec validation exige que les lignes existantes la respectent. Recherchez les violations et décidez de leur correction ou mise à l'écart. Remplir des valeurs absentes avec des défauts inventés uniquement pour réussir la migration change les données métier.

Une contrainte peut être active mais non approuvée si l'existant n'a pas été validé. Inspectez is_disabled et is_not_trusted dans sys.check_constraints. Pour une contrainte existante, WITH CHECK CHECK CONSTRAINT permet de vérifier les données et d'établir cette confiance. Planifiez le travail et le verrouillage sur une grande table.

Testez aussi les mises à jour. Passer de Draft à Published doit affecter PublishedAt dans la même instruction. Modifier le statut puis la date séparément crée un état intermédiaire interdit. Cet échec impose une transition cohérente.

Enfin, traduisez les erreurs en messages compréhensibles. La validation applicative précoce aide l'utilisateur, mais la base reste la protection finale. Si un état Archived apparaît, faites évoluer ensemble modèle, migration et tests de transition. Affaiblir la condition jusqu'à ce que le nouveau code passe ne garantit pas une règle correcte.

Références techniques: Microsoft Learn: CHECK constraints · Microsoft Learn: sys.check_constraints.