Wie kann ich eine eindeutige Einschränkung erstellen, die auch Nullen zulässt?

Question

Ich möchte eine eindeutige Einschränkung für eine Spalte haben, die ich mit GUIDs auffüllen werde. Meine Daten enthalten jedoch Nullwerte für diese Spalten. Wie erstelle ich die Einschränkung, die mehrere Nullwerte zulässt?

Hier ist ein Beispielszenario . Betrachten Sie dieses Schema:

CREATE TABLE People (
  Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY,
  Name NVARCHAR(250) NOT NULL,
  LibraryCardId UNIQUEIDENTIFIER NULL,
  CONSTRAINT UQ_People_LibraryCardId UNIQUE (LibraryCardId)
)

Dann sehen Sie in diesem Code, was ich zu erreichen versuche:

-- This works fine:
INSERT INTO People (Name, LibraryCardId) 
 VALUES ('John Doe', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');

-- This also works fine, obviously:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Marie Doe', 'BBBBBBBB-BBBB-BBBB-BBBB-BBBBBBBBBBBB');

-- This would *correctly* fail:
--INSERT INTO People (Name, LibraryCardId) 
--VALUES ('John Doe the Second', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');

-- This works fine this one first time:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Richard Roe', NULL);

-- THE PROBLEM: This fails even though I'd like to be able to do this:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Marcus Roe', NULL);

Die letzte Anweisung schlägt mit einer Meldung fehl:

Verstoß gegen die UNIQUE KEY-Beschränkung 'UQ_People_LibraryCardId'. Es kann kein doppelter Schlüssel in das Objekt 'dbo.People' eingefügt werden.

Wie kann ich mein Schema und/oder die Eindeutigkeitsbeschränkung so ändern, dass es mehrere NULL Werte, während gleichzeitig die Eindeutigkeit der tatsächlichen Daten überprüft wird?

Answer 1

Was Sie suchen, ist in der Tat Teil der ANSI-Standards SQL:92, SQL:1999 und SQL:2003, d.h. eine UNIQUE-Beschränkung muss doppelte Nicht-NULL-Werte verbieten, aber mehrere NULL-Werte akzeptieren.

In der Microsoft-Welt von SQL Server ist jedoch ein einzelner NULL-Wert erlaubt, aber mehrere NULL-Werte sind nicht zulässig...

Sur SQL Server 2008 können Sie einen eindeutigen gefilterten Index auf der Grundlage eines Prädikats definieren, das NULLs ausschließt:

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

In früheren Versionen können Sie auf VIEWS mit einem NOT NULL-Prädikat zurückgreifen, um die Einschränkung zu erzwingen.

Answer 2

SQL Server 2008 +

Sie können einen eindeutigen Index erstellen, der mehrere NULLs akzeptiert, mit einem WHERE Klausel. Siehe die Antwort unten .

Vor SQL Server 2008

Sie können keine UNIQUE-Beschränkung erstellen und NULLs zulassen. Sie müssen einen Standardwert für NEWID() festlegen.

Aktualisieren Sie die vorhandenen Werte auf NEWID() where NULL, bevor Sie die UNIQUE-Beschränkung erstellen.

Answer 3

SQL Server 2008 und höher

Filtern Sie einfach einen eindeutigen Index:

CREATE UNIQUE NONCLUSTERED INDEX UQ_Party_SamAccountName
ON dbo.Party(SamAccountName)
WHERE SamAccountName IS NOT NULL;

In niedrigeren Versionen ist eine materialisierte Ansicht immer noch nicht erforderlich

Bei SQL Server 2005 und früheren Versionen können Sie dies ohne eine Ansicht tun. Ich habe gerade eine eindeutige Einschränkung, wie Sie es wünschen, zu einer meiner Tabellen hinzugefügt. Da ich Eindeutigkeit in der Spalte SamAccountName Da ich aber mehrere NULLs zulassen möchte, habe ich eine materialisierte Spalte und keine materialisierte Ansicht verwendet:

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

Sie müssen einfach etwas in die berechnete Spalte eingeben, das garantiert eindeutig für die gesamte Tabelle ist, wenn die eigentlich gewünschte eindeutige Spalte NULL ist. In diesem Fall, PartyID ist eine Identitätsspalte und wird, da sie numerisch ist, niemals mit einer SamAccountName also hat es bei mir funktioniert. Sie können Ihre eigene Methode ausprobieren - stellen Sie sicher, dass Sie den Bereich Ihrer Daten verstehen, damit es keine Überschneidungen mit echten Daten gibt. Das könnte so einfach sein wie das Voranstellen eines Unterscheidungszeichens wie dieses:

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

Auch wenn PartyID wurde eines Tages nicht mehr numerisch und könnte mit einer SamAccountName Jetzt ist es egal.

Beachten Sie, dass das Vorhandensein eines Indexes, der die berechnete Spalte enthält, implizit dazu führt, dass jedes Ausdrucksergebnis zusammen mit den anderen Daten in der Tabelle auf der Festplatte gespeichert wird, was zusätzlichen Speicherplatz benötigt.

Beachten Sie, dass Sie, wenn Sie keinen Index wollen, immer noch CPU sparen können, indem Sie den Ausdruck auf der Festplatte vorberechnen lassen, indem Sie das Schlüsselwort PERSISTED an das Ende der Definition des Spaltenausdrucks.

In SQL Server 2008 und höher sollten Sie auf jeden Fall die gefilterte Lösung verwenden, wenn es möglich ist!

Kontroverse

Bitte beachten Sie, dass einige Datenbankspezialisten dies als einen Fall von "Surrogat-NULLs" ansehen werden, was definitiv Probleme mit sich bringt (hauptsächlich aufgrund von Problemen bei dem Versuch, zu bestimmen, wann etwas ein tatsächlicher Wert oder eine Ersatzwert für fehlende Daten Es kann auch zu Problemen mit der Anzahl der Nicht-NULL-Surrogatwerte kommen, die sich wie verrückt vermehren).

Ich glaube jedoch, dass dieser Fall anders gelagert ist. Die berechnete Spalte, die ich hinzufüge, wird nie dazu verwendet, etwas zu bestimmen. Sie hat keine eigene Bedeutung und kodiert keine Informationen, die nicht bereits separat in anderen, korrekt definierten Spalten zu finden sind. Sie sollte niemals ausgewählt oder verwendet werden.

Meine Geschichte ist also, dass dies kein Ersatz für NULL ist, und ich bleibe dabei! Da wir den Nicht-NULL-Wert eigentlich für keinen anderen Zweck brauchen, als um die UNIQUE Index zu verwenden, um NULLs zu ignorieren, hat unser Anwendungsfall keines der Probleme, die bei der normalen NULL-Surrogaterstellung auftreten.

All das gesagt, ich habe kein Problem mit der Verwendung einer indizierten Ansicht stattdessen-aber es bringt einige Probleme mit ihm wie die Anforderung der Verwendung SCHEMABINDING . Viel Spaß beim Hinzufügen einer neuen Spalte zu Ihrer Basistabelle (Sie müssen mindestens den Index löschen und dann den View löschen oder den View so ändern, dass er nicht schemagebunden ist). Siehe die vollständige (lange) Liste der Anforderungen für die Erstellung einer indizierten Ansicht in SQL Server (2005) (auch spätere Versionen), (2000) .

更新情報

Wenn Ihre Spalte numerisch ist, kann es eine Herausforderung sein, sicherzustellen, dass die eindeutige Einschränkung mit Coalesce führt nicht zu Kollisionen. In diesem Fall gibt es einige Möglichkeiten. Eine könnte darin bestehen, eine negative Zahl zu verwenden, die "Ersatz-NULLs" nur in den negativen Bereich und die "echten Werte" nur in den positiven Bereich zu legen. Alternativ könnte auch das folgende Muster verwendet werden. In der Tabelle Issue (wobei IssueID ist die PRIMARY KEY ), kann es sein, dass es eine TicketID aber wenn es eine gibt, muss sie eindeutig sein.

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);

Wenn IssueID 1 das Ticket 123 hat, wird die UNIQUE Einschränkung auf die Werte (123, NULL) sein. Wenn IssueID 2 kein Ticket hat, gilt sie für (NULL, 2). Ein wenig Nachdenken wird zeigen, dass diese Einschränkung für keine Zeile in der Tabelle dupliziert werden kann und trotzdem mehrere NULLs erlaubt.

Answer 4

Für Personen, die Folgendes verwenden Microsoft SQL Server Manager und einen eindeutigen, aber nullbaren Index erstellen möchten, können Sie Ihren eindeutigen Index wie gewohnt erstellen. Wählen Sie dann in den Indexeigenschaften für Ihren neuen Index "Filter" auf der linken Seite aus und geben Sie Ihren Filter ein (d. h. Ihre Where-Klausel). Er sollte etwa so lauten:

([YourColumnName] IS NOT NULL)

Dies funktioniert mit MSSQL 2012

Answer 5

Dies kann auch im Designer geschehen

Klicken Sie mit der rechten Maustaste auf den Index > Eigenschaften um dieses Fenster zu erhalten