Frage bedingte eindeutige Einschränkung


Ich habe eine Situation, wo ich eine eindeutige Einschränkung für eine Reihe von Spalten erzwingen muss, aber nur für einen Wert einer Spalte.

So habe ich zum Beispiel eine Tabelle wie Table (ID, Name, RecordStatus).

RecordStatus kann nur den Wert 1 oder 2 (aktiv oder gelöscht) haben, und ich möchte nur dann eine eindeutige Einschränkung für (ID, RecordStatus) erstellen, wenn RecordStatus = 1 ist, da es mir egal ist, ob mehrere gelöschte Datensätze mit demselben Inhalt vorhanden sind ICH WÜRDE.

Kann ich das tun, außer Auslöser zu schreiben?

Ich verwende SQL Server 2005.


76
2018-05-14 21:57


Ursprung


Antworten:


Fügen Sie eine Prüfbedingung wie diese hinzu. Der Unterschied ist, dass Sie false zurückgeben, wenn Status = 1 und Count> 0 ist.

http://msdn.microsoft.com/en-us/library/ms188258.aspx

CREATE TABLE CheckConstraint
(
  Id TINYINT,
  Name VARCHAR(50),
  RecordStatus TINYINT
)
GO

CREATE FUNCTION CheckActiveCount(
  @Id INT
) RETURNS INT AS BEGIN

  DECLARE @ret INT;
  SELECT @ret = COUNT(*) FROM CheckConstraint WHERE Id = @Id AND RecordStatus = 1;
  RETURN @ret;

END;
GO

ALTER TABLE CheckConstraint
  ADD CONSTRAINT CheckActiveCountConstraint CHECK (NOT (dbo.CheckActiveCount(Id) > 1 AND RecordStatus = 1));

INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 1);

INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 2);
-- Msg 547, Level 16, State 0, Line 14
-- The INSERT statement conflicted with the CHECK constraint "CheckActiveCountConstraint". The conflict occurred in database "TestSchema", table "dbo.CheckConstraint".
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);

SELECT * FROM CheckConstraint;
-- Id   Name         RecordStatus
-- ---- ------------ ------------
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  1
-- 2    Oh no!       1
-- 2    Oh no!       2

ALTER TABLE CheckConstraint
  DROP CONSTRAINT CheckActiveCountConstraint;

DROP FUNCTION CheckActiveCount;
DROP TABLE CheckConstraint;

34
2018-05-14 22:06



Erblicken, der gefilterte Index. Aus der Dokumentation (Hervorhebung von mir):

Ein gefilterter Index ist ein optimierter Nonclustered-Index, der besonders geeignet ist, um Abfragen abzudecken, die aus einer wohldefinierten Teilmenge von Daten auswählen. Es verwendet ein Filterprädikat, um einen Teil der Zeilen in der Tabelle zu indizieren. Ein gut durchdachter gefilterter Index kann die Abfrageleistung verbessern und die Indexwartungs- und Speicherkosten im Vergleich zu Volltabellen-Indizes verringern.

Und hier ist ein Beispiel, das einen eindeutigen Index mit einem Filterprädikat kombiniert:

create einzigartig index [MyIndex]
on [MyTable]([ID])
wo [RecordStatus] = 1

Dies erzwingt im Wesentlichen die Einzigartigkeit von ID wann RecordStatus ist 1.

Hinweis: Der gefilterte Index wurde in SQL Server 2008 eingeführt. Informationen zu früheren Versionen von SQL Server finden Sie unter diese Antwort.


112
2018-03-01 00:37



Sie könnten die gelöschten Datensätze in eine Tabelle verschieben, für die die Einschränkung fehlt, und möglicherweise eine Ansicht mit UNION der beiden Tabellen verwenden, um das Erscheinungsbild einer einzelnen Tabelle zu erhalten.


9
2018-05-14 22:01



Du kannst das auf wirklich hacky Weise machen ...

Erstellen Sie eine schematische Ansicht auf Ihrem Tisch.

CREATE VIEW Was auch immer SELECT * FROM Tabelle WHERE RecordStatus = 1

Erstellen Sie jetzt eine eindeutige Einschränkung für die Ansicht mit den gewünschten Feldern.

Eine Anmerkung zu Schemabound-Ansichten: Wenn Sie die zugrunde liegenden Tabellen ändern, müssen Sie die Ansicht neu erstellen. Aus diesem Grund gab es viele Fehler.


3
2018-05-14 22:43



Da Duplikate zulässig sind, funktioniert eine eindeutige Einschränkung nicht. Sie können eine Prüfbedingung für die RecordStatus-Spalte und eine gespeicherte Prozedur für INSERT erstellen, die vor dem Einfügen doppelter IDs die vorhandenen aktiven Datensätze überprüft.


1
2018-05-14 21:59



Wenn Sie NULL nicht als RecordStatus verwenden können, wie von Bill vorgeschlagen, können Sie seine Idee mit einem funktionsbasierten Index kombinieren. Erstellen Sie eine Funktion, die NULL zurückgibt, wenn der RecordStatus nicht einer der Werte ist, die Sie in Ihrer Einschränkung berücksichtigen möchten (und ansonsten den RecordStatus), und erstellen Sie einen Index darüber.

Das hat den Vorteil, dass Sie andere Zeilen in der Tabelle in Ihrer Einschränkung nicht explizit untersuchen müssen, was zu Leistungsproblemen führen kann.

Ich sollte sagen, dass ich SQL-Server überhaupt nicht kenne, aber ich habe diesen Ansatz in Oracle erfolgreich verwendet.


1
2018-05-14 22:48