Problématique :

On a besoin sur une table d’ajouter une contrainte d’intégrité qui est la suivante : je veux que les valeurs de mon champ soient uniques ou NULL (i.e. uniques sauf si elles sont nulles).  J’ai rencontré souvent cette utilisation pour stocker des utilisateurs où l’on souhaite avoir l’unicité des N° de téléphone mais où ce même numéro n’est pas une saisie obligatoire.

La solution triviale à laquelle on pense tout de suite est de mettre un index unique sur le champ. Mais on s’aperçoit vite que ça ne marche pas car à la 2^ème valeur nulle insérée, on viole l’intégrité alors que, fonctionnellement, la saisie est correcte.

Solutions :

Je vous propose 2 solutions :

• Utiliser une contrainte d’intégrité sur la table
•  Utiliser une vue indexée

Check Constraint :

La première solution est d’utiliser une CHECK CONSTRAINT qui nous permet de « coder » une contrainte d’intégrité sur une table. Pour rappel, voici la syntaxe d’une CHECK CONSTRAINT :

ALTER TABLE <MaTable>
ADD CONSTRAINT <CK_NAME>
CHECK  ( <ici la contrainte> )

On serait donc tenté d’écrire un truc du genre :

CHECK ( [Phonenumber] IS NULL OR NOT EXISTS (SELECT * FROM Person.[Contact] U WHERE U.[Phonenumber] = [Phonenumber] ))

Sauf que, si vous essayez d’exécuter cette instruction vous aurez l’erreur suivante :

Subqueries are not allowed in this context. Only scalar expressions are allowed.

La solution pour contourner ce message d’erreur est de créer une fonction qui encapsule la sous-requête. La contrainte devient :

CHECK ([Phonenumber] IS NULL OR [dbo].[PhoneExists]([Phonenumber])=0)

Nous avons donc besoin d’une fonction scalaire qui renvoie un type bit. Vous noterez que l’on est obligé de faire un test sur le résultat car le type bit n’est pas utilisable en T-SQL comme un booléen d’un langage de programmation.

Voici le code de la fonction :

CREATE FUNCTION [dbo].[PhoneExists]
(
      @Phone as char(10)
)
RETURNS bit
AS
BEGIN
      declare @result  bit

      if Exists(Select * From Person.Contact Where Phonenumber = @Phone)
            set @result = 1
      else
            set @result = 0

      return  @result
END

Vue indexée

L’autre solution est d’utiliser une vue indexée. Mais pourquoi me direz vous, une vue indexée étant conseillée pour faire de l’optimisation de requête, en quoi peut-elle influer sur l’intégrité des données ?

Vous allez vite comprendre : une vue indexée est composée d’un index classique, qui peut donc être UNIQUE ; or cet index se place sur une vue c’est-à-dire sur un sous-ensemble des données. Donc on utilise ce système pour mettre une contrainte d’unicité sur un sous-ensemble de la table ; ici on prendra les données non nulles.

Créons tout d’abord la vue :

Create View [dbo].[vwUser_Phone_UNIQUE]
With SchemaBinding
As
Select Phonenumber
From Person.Contact
Where Phonenumber is not null

En travaillant avec cette vue, je ne me concentre que sur les utilisateurs ayant saisi leur numéro de téléphone.

Vous noterez l’instruction With SchemaBinding qui est nécessaire pour pouvoir créer un index sur une vue.

Créons maintenant l’index :

CREATE UNIQUE CLUSTERED INDEX [IX_USER_PHONE_UNIQUE]
ON [dbo].[vwUser_Phone_UNIQUE]
(
      [Phone] ASC
)

L’unicité sur le champ Phone va garantir l’intégrité des numéros renseignés. Note : l’index est créé comme CLUSTERED car c’est un index sur une vue (et il lui faut donc un stockage physique).

Conclusion :

2 méthodes pour un résultat identique ; que choisir ? Pour répondre à cette question, il faut étudier les solutions sur différents angles : maintenabilité, technicité, performances, etc. et voir si cela colle avec votre façon de développer (et d’administrer).

Côté performances, je joins une petite trace SQL qui ne résout pas la question (car il faudrait faire une étude de perf. plus poussée) mais qui milite en faveur de la CHECK CONSTRAINT. Moi, je suis plutôt vue indexée.

Variantes :

Ca marche aussi pour les contraintes « unique ou chaîne vide », « unique ou ‘cohiba’ », etc. 😉