Mon besoin d’une contrainte conditionnelle s’était exprimé sur une contrainte de type UNIQUE, que je voulais restreindre à certaines lignes, c’est à dire les lignes pour lesquelles une condition du type MaColonne = X est satisfaite. Ce billet montre 2 manières différente de les créer.
Prenons l’exemple suivant :
CREATE TABLE Moron ( Id INT NOT NULL, Name NVARCHAR(25), IsDeleted BIT NOT NULL, CONSTRAINT UQ_Moron_Name UNIQUE(Name) )
On définit cette table comme étant les abrutis présents dans une classe.
On sait qu’il ne peut pas y avoir 2 abrutis avec le même nom dans une classe, on tape donc dans le marbre la contrainte UQ_Moron_Name pour que cette valeur de vérité soit toujours vérifiée.
Supposons maintenant qu’un abruti Aurélien s’en aille, on va le marquer IsDeleted = 1 .
Supposons ensuite, qu’un autre abruti arrive en classe et qu’il s’appelle aussi Aurélien. Boum, on ne peut pas l’insérer dans la table à cause de la contrainte d’intégrité.
L’idéal serait donc de modifier cette contrainte (ça serait vraiment dommage de la supprimer, non ?), et ainsi prendre en compte cette nouvelle réalité, à savoir que ce qui nous intéresse est : il ne peut pas y avoir 2 abrutis avec le même nom dans une classe, c’est à dire que c’est la contrainte précédente mais uniquement lorsque IsDeleted = 0 .
Et donc ?
Il faut trouver le moyen de réaliser une contrainte conditionnelle, et deux moyens au moins s’offrent à nous :
La vue
On peut créer une vue telle que définie:
CREATE View ActiveMoron AS SELECT Name FROM Moron WHERE IsDeleted = 0 GO CREATE UNIQUE CLUSTERED INDEX IDX_Active_Moron ON ActiveMoron(Name)
On raffine ainsi la table aux colonnes essentielles à la condition (avec le SELECT ), et aux lignes sur lesquelles on doit l’appliquer (avec le WHERE ).
Il n’y a plus qu’à poser la contrainte, ici via un index UNIQUE .
L’index conditionnel
Disponible à partir de SQL Server 2008, il est possible de poser des index contenant une condition. Ainsi
CREATE UNIQUE INDEX IDX_Active_Moron ON ActiveMoron(Name) WHERE IsDeleted = 0
est complètement équivalent à la solution précédente, du point de vue de la contrainte, mais la vue en moins.