Comment puis-je créer une contrainte unique qui permet aussi nuls?

Question

Je veux une contrainte unique sur une colonne, je vais remplir avec un GUID. Toutefois, les données contiennent des valeurs null. Comment puis-je créer la contrainte, mais d'autoriser les valeurs null?

Answer 1

Ce que vous cherchez est en effet partie des normes ANSI SQL:92, SQL:1999 et SQL:2003, c'est à dire une contrainte UNIQUE doit interdire le double des valeurs non NULLES, mais accepte plusieurs valeurs NULL.

Dans le monde Microsoft de SQL Server toutefois, un seul NULL sont autorisées, mais plusieurs valeurs Null ne sont pas...

Dans SQL Server 2008, vous pouvez définir un unique index filtré basé sur un prédicat qui exclut les valeurs Null:

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

Dans les versions antérieures, vous pouvez recourir à des points de VUE avec not NULL prédicat à faire respecter la contrainte.

Answer 2

Avant SQL Server 2008

Vous ne pouvez pas créer une contrainte UNIQUE et autoriser les valeurs Null. Vous devez définir une valeur par défaut de la fonction NEWID().

Mettre à jour les valeurs existantes pour NEWID() où NULL avant de créer une contrainte UNIQUE.

SQL Server 2008 +

Vous pouvez créer un index unique avec un WHERE de la clause.

Answer 3

Vue Matérialisée Pas Nécessaire

Pour SQL 2005 et les versions antérieures, vous pouvez le faire sans vue. Je viens d'ajouter une contrainte unique comme vous êtes en demandant à l'un de mes tableaux. Étant donné que je veux unicité dans la colonne SamAccountName, mais je veux permettre à plusieurs Zéros, j'ai utilisé un matérialisé colonne plutôt qu'une vue matérialisée:

ALTER TABLE dbo.Party ADD SamAccountNameUnique
   AS (Coalesce(SamAccountName, Convert(varchar(11), PartyID)))
ALTER TABLE dbo.Party ADD CONSTRAINT UQ_Party_SamAccountName
   UNIQUE (SamAccountNameUnique)

Il vous suffit de mettre quelque chose dans la colonne calculée qui sera garantie unique sur l'ensemble de la table quand le réel désiré unique colonne est NULL. Dans ce cas, PartyID est une colonne d'identité et d'être numérique n'égaleront jamais tout SamAccountName, si cela a fonctionné pour moi. Vous pouvez essayer votre propre méthode, assurez-vous de comprendre le domaine de vos données de sorte qu'il n'y a pas de possibilité de croisement avec des données réelles. Cela pourrait être aussi simple que d'ajouter un élément de différenciation de caractère comme ceci:

Coalesce('n' + SamAccountName, 'p' + Convert(varchar(11), PartyID))

Même si PartyID sont devenus non-numérique, un jour, et pourrait coïncider avec un SamAccountName, maintenant, il ne sera pas question.

Cela NE prend de la place dans le tableau, ce qui peut ne pas être le meilleur pour vous, si. Dans SQL Server 2008 et, certainement utiliser la solution filtrée!

Notez que vous pouvez aussi ajouter le mot-clé PERSISTED à la fin de la définition de la colonne si vous ne voulez pas un indice, mais souhaitez utiliser l'espace disque pour le calcul préalable de l'expression dans le but d'économiser du CPU plus tard.

La controverse

Veuillez noter que certaines base de données de professionnels de la voir comme un cas de "porteuse de valeurs Null", qui certainement ont des problèmes (principalement en raison de problèmes autour d'essayer de déterminer quand quelque chose est une valeur réelle ou une substitution de la valeur des données manquantes; il peut aussi y avoir des problèmes avec le nombre de non-NULL porteuse de valeurs multipliant comme un fou).

Cependant, je crois que ce cas est différent. La colonne calculée, je suis en ajoutant ne sera jamais utilisée pour déterminer quoi que ce soit. Il n'a pas de sens de lui-même, et pas de code pour des informations qui ne sont pas déjà trouvé séparément dans d'autres, de la définir correctement les colonnes. Il ne doit jamais être sélectionné ou utilisé.

Donc, mon histoire, c'est que ce n'est pas un substitut à la valeur NULL, et je suis coller à lui! Puisque nous ne voulons effectivement la valeur non-NULL pour un autre but que de tromper l' UNIQUE indice d'ignorer les valeurs Null, notre cas d'utilisation a aucun des problèmes qui se posent avec la normale de substitution NULLE création.

Cela dit, je n'ai aucun problème avec l'aide d'une vue indexée au lieu-mais il apporte avec lui des questions telles que l'exigence de l'utilisation de SCHEMABINDING. Amusez-vous en ajoutant une nouvelle colonne de votre table de base (que vous aurez au minimum supprimer l'index, puis baisse de la vue ou de modifier l'affichage de ne pas être liée au schéma). Voir toute la (longue) liste des exigences pour la création d'une vue indexée dans SQL Server.

Mise à jour

Si votre colonne est numérique, il y a peut être un défi de s'assurer que la contrainte unique à l'aide de Coalesce n'entraîne pas de collisions. Dans ce cas, il y a quelques options. On pourrait être d'utiliser un nombre négatif, de mettre la "porteuse de valeurs Null" uniquement dans la plage négative, et les "vraies valeurs" que dans la plage positive. Alternativement, le schéma suivant peut être utilisé. Dans le tableau Issue (où IssueID est le PRIMARY KEY), il peut ou peut ne pas être une TicketID,, mais si il y en a un, il doit être unique.

ALTER TABLE dbo.Issue ADD TicketUnique
   AS (CASE WHEN TicketID IS NULL THEN IssueID END);
ALTER TABLE dbo.Issue ADD CONSTRAINT UQ_Issue_Ticket_AllowNull
   UNIQUE (TicketID, TicketUnique);

Si IssueID 1 a billet 123, UNIQUE contrainte sur les valeurs (123, NULL). Si IssueID 2 n'a pas de billet, il sera sur (NULL, 2). Certains pensaient montrera que cette contrainte ne peut pas être dupliqué pour toute ligne dans la table, et permet encore plusieurs valeurs Null.

Answer 4

Lorsque j'ai appliqué l'index unique ci-dessous:

CREATE UNIQUE NONCLUSTERED INDEX idx_badgeid_notnull
ON employee(badgeid)
WHERE badgeid IS NOT NULL;

tous les non-nulle de mise à jour et de l'insertion a échoué avec l'erreur ci-dessous:

La mise à JOUR a échoué parce que la suite de DÉFINIR les options ont des paramètres incorrects: 'ARITHABORT'.

J'ai trouvé ceci sur MSDN

SET ARITHABORT doit être SUR lors de la création ou de la modification d'index sur des colonnes calculées ou des vues indexées. Si SET ARITHABORT est ÉTEINT, CREATE, UPDATE, INSERT et DELETE sur des tables avec des index sur des colonnes calculées ou des vues indexées échouent.

Donc, pour obtenir que cela fonctionne correctement, je l'ai fait

À droite, cliquez sur [Base de données]-->Propriétés-->Options-->Autres Options-->Misscellaneous-->annulation Arithmétique Activé -->vrai

Je crois qu'il est possible d'activer cette option dans le code à l'aide de

ALTER DATABASE "DBNAME" SET ARITHABORT ON

mais je n'ai pas testé

Answer 5

Créer une vue qui ne sélectionne que les non-NULL de colonnes et de créer l' UNIQUE INDEX sur la vue:

CREATE VIEW myview
AS
SELECT  *
FROM    mytable
WHERE   mycolumn IS NOT NULL

CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn)

Notez que vous aurez besoin pour effectuer l' INSERTs et UPDATEs'sur la vue au lieu de la table.

Vous pouvez le faire avec un INSTEAD OF trigger:

CREATE TRIGGER trg_mytable_insert ON mytable
INSTEAD OF INSERT
AS
BEGIN
        INSERT
        INTO    myview
        SELECT  *
        FROM    inserted
END