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

Als ich den eindeutigen Index unten angewendet habe:

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

jede Aktualisierung und Einfügung, die nicht null ist, schlug mit dem folgenden Fehler fehl:

UPDATE ist fehlgeschlagen, weil die folgenden SET-Optionen falsch eingestellt sind: 'ARITHABORT'.

Ich fand dies auf MSDN

SET ARITHABORT muss ON sein, wenn Sie Indizes auf berechneten Spalten oder indizierten Ansichten erstellen oder ändern. Wenn SET ARITHABORT OFF ist, schlagen CREATE-, UPDATE-, INSERT- und DELETE-Anweisungen für Tabellen mit Indizes auf berechneten Spalten oder indizierten Ansichten fehl.

Damit dies korrekt funktioniert, habe ich Folgendes getan

Rechtsklick [Datenbank]-->Eigenschaften-->Optionen-->Andere Optionen-->Vermischtes-->Arithmetischer Abbruch aktiviert -->true

Ich glaube, es ist möglich, diese Option im Code zu setzen, indem man

ALTER DATABASE "DBNAME" SET ARITHABORT ON

aber ich habe dies nicht getestet

Answer 2

Erstellen Sie eine Ansicht, die nur nicht NULL Spalten und erstellen Sie die UNIQUE INDEX auf die Aussicht:

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

CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn)

Beachten Sie, dass Sie folgende Schritte durchführen müssen INSERT und UPDATE in der Ansicht statt in der Tabelle.

Sie können dies mit einer INSTEAD OF Auslöser:

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

Answer 3

Es ist möglich, eine eindeutige Einschränkung für eine geclusterte indizierte Ansicht zu erstellen

Sie können die Ansicht wie folgt erstellen:

CREATE VIEW dbo.VIEW_OfYourTable WITH SCHEMABINDING AS
SELECT YourUniqueColumnWithNullValues FROM dbo.YourTable
WHERE YourUniqueColumnWithNullValues IS NOT NULL;

---

und die eindeutige Einschränkung wie folgt:

CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_OFYOURTABLE 
  ON dbo.VIEW_OfYourTable(YourUniqueColumnWithNullValues)

Answer 4

Meiner Erfahrung nach - wenn Sie denken, dass eine Spalte NULLs zulassen muss, aber auch UNIQUE für Werte sein muss, wo sie existieren, modellieren Sie die Daten möglicherweise falsch. Dies deutet oft darauf hin, dass Sie eine separate Unterentität innerhalb derselben Tabelle als eine andere Entität erstellen. Es ist wahrscheinlich sinnvoller, diese Entität in einer zweiten Tabelle zu haben.

In dem angegebenen Beispiel würde ich LibraryCardId in eine separate LibraryCards-Tabelle mit einem eindeutigen Fremdschlüssel (nicht null) zur Tabelle People aufnehmen:

CREATE TABLE People (
  Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY,
  Name NVARCHAR(250) NOT NULL,
)
CREATE TABLE LibraryCards (    
  LibraryCardId UNIQUEIDENTIFIER CONSTRAINT PK_LibraryCards PRIMARY KEY,
  PersonId INT NOT NULL
  CONSTRAINT UQ_LibraryCardId_PersonId UNIQUE (PersonId),
  FOREIGN KEY (PersonId) REFERENCES People(id)
)

Auf diese Weise müssen Sie sich nicht darum kümmern, dass eine Spalte sowohl eindeutig als auch löschbar ist. Wenn eine Person keinen Bibliotheksausweis hat, gibt es einfach keinen Datensatz in der Tabelle Bibliotheksausweise. Wenn es zusätzliche Attribute zum Bibliotheksausweis gibt (z. B. das Ablaufdatum), haben Sie jetzt einen logischen Ort, um diese Felder unterzubringen.

Answer 5

Vielleicht sollten Sie einen " INSTEAD OF "auslösen und die Prüfung selbst durchführen? Mit einem nicht geclusterten (nicht eindeutigen) Index für die Spalte, um die Suche zu ermöglichen.