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

Wie bereits erwähnt, implementiert SQL Server nicht den ANSI-Standard, wenn es um die UNIQUE CONSTRAINT . Es gibt eine Ticket auf Microsoft Connect seit 2007. Wie dort vorgeschlagen und aquí die besten Optionen sind derzeit die Verwendung eines gefilterten Indexes, wie er in eine weitere Antwort oder eine berechnete Spalte, z. B.:

CREATE TABLE [Orders] (
  [OrderId] INT IDENTITY(1,1) NOT NULL,
  [TrackingId] varchar(11) NULL,
  ...
  [ComputedUniqueTrackingId] AS (
      CASE WHEN [TrackingId] IS NULL
      THEN '#' + cast([OrderId] as varchar(12))
      ELSE [TrackingId_Unique] END
  ),
  CONSTRAINT [UQ_TrackingId] UNIQUE ([ComputedUniqueTrackingId])
)

Answer 2

Sie können eine ANSTELLE VON Auslöser, um auf bestimmte Bedingungen zu prüfen und einen Fehler zu melden, wenn diese erfüllt sind. Die Erstellung eines Indexes kann bei größeren Tabellen kostspielig sein.

Hier ist ein Beispiel:

CREATE TRIGGER PONY.trg_pony_unique_name ON PONY.tbl_pony
 INSTEAD OF INSERT, UPDATE
 AS
BEGIN
 IF EXISTS(
    SELECT TOP (1) 1 
    FROM inserted i
    GROUP BY i.pony_name
    HAVING COUNT(1) > 1     
    ) 
     OR EXISTS(
    SELECT TOP (1) 1 
    FROM PONY.tbl_pony t
    INNER JOIN inserted i
    ON i.pony_name = t.pony_name
    )
    THROW 911911, 'A pony must have a name as unique as s/he is. --PAS', 16;
 ELSE
    INSERT INTO PONY.tbl_pony (pony_name, stable_id, pet_human_id)
    SELECT pony_name, stable_id, pet_human_id
    FROM inserted
 END

Answer 3

Sie können dies nicht mit einer UNIQUE Einschränkung, aber Sie können dies in einem Trigger tun.

    CREATE TRIGGER [dbo].[OnInsertMyTableTrigger]
   ON  [dbo].[MyTable]
   INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @Column1 INT;
    DECLARE @Column2 INT; -- allow nulls on this column

    SELECT @Column1=Column1, @Column2=Column2 FROM inserted;

    -- Check if an existing record already exists, if not allow the insert.
    IF NOT EXISTS(SELECT * FROM dbo.MyTable WHERE Column1=@Column1 AND Column2=@Column2 @Column2 IS NOT NULL)
    BEGIN
        INSERT INTO dbo.MyTable (Column1, Column2)
            SELECT @Column2, @Column2;
    END
    ELSE
    BEGIN
        RAISERROR('The unique constraint applies on Column1 %d, AND Column2 %d, unless Column2 is NULL.', 16, 1, @Column1, @Column2);
        ROLLBACK TRANSACTION;   
    END

END

Answer 4

CREATE UNIQUE NONCLUSTERED INDEX [UIX_COLUMN_NAME]
ON [dbo].[Employee]([Username] ASC) WHERE ([Username] IS NOT NULL) 
WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, 
MAXDOP = 0) ON [PRIMARY];

Answer 5

Dieser Code, wenn Sie ein Registrierungsformular mit textBox und verwenden Sie einfügen und ur textBox ist leer und Sie klicken Sie auf die Schaltfläche Senden .

CREATE UNIQUE NONCLUSTERED INDEX [IX_tableName_Column]
ON [dbo].[tableName]([columnName] ASC) WHERE [columnName] !=`''`;