Fügen Sie eine eindeutige Einschränkung zur Kombination von zwei Spalten hinzu

Question

Ich habe eine Tabelle und irgendwie ist dieselbe Person zweimal in meine Person-Tabelle gelangt. Der Primärschlüssel ist derzeit nur eine fortlaufende Nummer, aber es gibt zwei weitere Felder, die ich eindeutig erzwingen möchte.

Zum Beispiel sind die Felder:

ID  
Name  
Aktiv  
PersonNummer  

Ich möchte nur 1 Datensatz mit einer eindeutigen PersonNummer und Aktiv = 1 haben.
(Die Kombination der beiden Felder muss also eindeutig sein)

Was ist der beste Weg in einer vorhandenen Tabelle in SQL Server, um sicherzustellen, dass, wenn jemand sonst einen Wert einfügt, der bereits vorhanden ist, der Vorgang fehlschlägt, sodass ich mir in meinem Anwendungscode keine Sorgen machen muss.

Answer 1

Sobald Sie Ihr Duplikat(en) entfernt haben:

ALTER TABLE dbo.yourtablename
  ADD CONSTRAINT uq_yourtablename UNIQUE(column1, column2);

oder

CREATE UNIQUE INDEX uq_yourtablename
  ON dbo.yourtablename(column1, column2);

Natürlich ist es oft besser, diese Verletzung zuerst zu überprüfen, bevor Sie SQL Server einfach versuchen lassen, die Zeile einzufügen und eine Ausnahme zurückzugeben (Ausnahmen sind teuer).

• Leistungsbeeinflussung unterschiedlicher Fehlerbehandlungstechniken

• Überprüfung möglicher Verletzungen von Einschränkungen, bevor TRY/CATCH betreten wird

Wenn Sie möchten, dass Ausnahmen nicht bis zur Anwendung durchsickern, ohne Änderungen an der Anwendung vorzunehmen, können Sie einen INSTEAD OF Trigger verwenden:

CREATE TRIGGER dbo.BlockDuplicatesYourTable
 ON dbo.YourTable
 INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  IF NOT EXISTS (SELECT 1 FROM inserted AS i 
    INNER JOIN dbo.YourTable AS t
    ON i.column1 = t.column1
    AND i.column2 = t.column2
  )
  BEGIN
    INSERT dbo.YourTable(column1, column2, ...)
      SELECT column1, column2, ... FROM inserted;
  END
  ELSE
  BEGIN
    PRINT 'Habe nichts gemacht.';
  END
END
GO

Aber wenn Sie dem Benutzer nicht mitteilen, dass er die Einfügung nicht vorgenommen hat, wird er sich fragen, warum die Daten nicht vorhanden sind und keine Ausnahme gemeldet wurde.

---

BEARBEITEN hier ist ein Beispiel, das genau das tut, was Sie verlangen, sogar mit denselben Namen wie in Ihrer Frage, und beweist es. Sie sollten es ausprobieren, bevor Sie davon ausgehen, dass die obigen Ideen nur eine Spalte behandeln oder die andere im Gegensatz zur Kombination...

USE tempdb;
GO

CREATE TABLE dbo.Person
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(32),
  Active BIT,
  PersonNumber INT
);
GO

ALTER TABLE dbo.Person 
  ADD CONSTRAINT uq_Person UNIQUE(PersonNumber, Active);
GO

-- hat Erfolg:
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 1, 22);
GO

-- hat Erfolg:
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 0, 22);
GO

-- fehlschlägt:
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 1, 22);
GO

Daten in der Tabelle nach all dem:

ID   Name   Active PersonNumber
---- ------ ------ ------------
1    foo    1      22
2    foo    0      22

Fehlermeldung bei der letzten Einfügung:

Msg 2627, Level 14, State 1, Line 3 Verletzung des eindeutigen Schlüsselconstraints 'uq_Person'. Kann doppelten Schlüssel in Objekt 'dbo.Person' nicht einfügen. Der Befehl wurde beendet.

Außerdem habe ich kürzlich mehr über eine Lösung zum Anwenden eines eindeutigen Constraints auf zwei Spalten in beliebiger Reihenfolge gebloggt:

• Setzen Sie einen eindeutigen Constraint, bei dem die Reihenfolge nicht wichtig ist

Answer 2

Dies kann auch im GUI erledigt werden:

1. Unter der Tabelle "Person" mit der rechten Maustaste auf Indizes klicken
2. Auf Neuer Index klicken/hovern
3. Auf Nicht gruppierten Index... klicken

4. Ein Standard Indexname wird vergeben, den Sie möglicherweise ändern möchten.
5. Das Kästchen Eindeutig ankreuzen
6. Den Button Hinzufügen... klicken

7. Die Spalten ankreuzen, die Sie einbeziehen möchten

8. In jedem Fenster auf OK klicken

Answer 3

In meinem Fall musste ich viele Inaktive zulassen und nur eine Kombination von zwei aktiven Schlüsseln aktivieren, so:

UUL_USR_IDF  UUL_UND_IDF    UUL_ATUAL
137          18             0
137          19             0
137          20             1
137          21             0

Dies scheint zu funktionieren:

CREATE UNIQUE NONCLUSTERED INDEX UQ_USR_UND_UUL_USR_IDF_UUL_ATUAL
ON USER_UND(UUL_USR_IDF, UUL_ATUAL)
WHERE UUL_ATUAL = 1;

Hier sind meine Testfälle:

SELECT * FROM USER_UND WHERE UUL_USR_IDF = 137

insert into USER_UND values (137, 22, 1) --ICH KANN NICHT => Doppelter Schlüssel kann nicht in Objekt 'dbo.USER_UND' mit eindeutigem Index 'UQ_USR_UND_UUL_USR_IDF_UUL_ATUAL' eingefügt werden. Der doppelte Schlüsselwert ist (137, 1).
insert into USER_UND values (137, 23, 0) --ICH KANN
insert into USER_UND values (137, 24, 0) --ICH KANN

DELETE FROM USER_UND WHERE UUL_USR_ID = 137

insert into USER_UND values (137, 22, 1) --ICH KANN
insert into USER_UND values (137, 27, 1) --ICH KANN NICHT => Doppelter Schlüssel kann nicht in Objekt 'dbo.USER_UND' mit eindeutigem Index 'UQ_USR_UND_UUL_USR_IDF_UUL_ATUAL' eingefügt werden. Der doppelte Schlüsselwert ist (137, 1).
insert into USER_UND values (137, 28, 0) --ICH KANN
insert into USER_UND values (137, 29, 0) --ICH KANN

Answer 4

Und wenn Sie viele INSERT-Abfragen haben, aber nicht jedes Mal eine FEHLERMeldung erhalten möchten, können Sie dies tun:

CREATE UNIQUE NONCLUSTERED INDEX SK01 ON dbo.Person(ID,Name,Active,PersonNumber) 
WITH(IGNORE_DUP_KEY = ON)