contrainte unique conditionnelle

Question

Je suis dans une situation où je dois appliquer une contrainte unique sur un ensemble de colonnes, mais seulement pour une valeur d'une colonne.

Par exemple, j'ai une table comme Table(ID, Name, RecordStatus).

RecordStatus ne peut avoir que la valeur 1 ou 2 (actif ou supprimé), et je veux créer une contrainte unique sur (ID, RecordStatus) uniquement lorsque RecordStatus = 1, puisque je ne me soucie pas de savoir s'il existe plusieurs enregistrements supprimés avec le même ID.

En dehors de l'écriture des déclencheurs, puis-je faire cela ?

J'utilise SQL Server 2005.

Answer 1

Regardez, l'indice filtré . Extrait de la documentation (c'est moi qui souligne) :

Un index filtré est un index optimisé non groupé particulièrement adapté aux requêtes qui sélectionnent un sous-ensemble bien défini de données. Il utilise un prédicat de filtre pour indexer une partie des lignes de la table. Un index filtré bien conçu peut améliorer les performances des requêtes et réduire les coûts de maintenance et de stockage de l'index par rapport aux index de tables complètes.

Et voici un exemple combinant un index unique avec un prédicat de filtre :

create unique index MyIndex
on MyTable(ID)
where RecordStatus = 1;

Cela renforce essentiellement l'unicité de ID quand RecordStatus est 1 .

Après la création de cet index, une violation de l'unicité entraînera l'émission d'un message d'avertissement :

Msg 2601, Niveau 14, Etat 1, Ligne 13
Impossible d'insérer une ligne de clé dupliquée dans l'objet 'dbo.MyTable' avec l'index unique 'MyIndex'. La valeur de la clé dupliquée est (9999).

_{Remarque : l'index filtré a été introduit dans SQL Server 2008. Pour les versions antérieures de SQL Server, veuillez consulter <a href="https://stackoverflow.com/a/866100/621962">cette réponse </a>.}

Answer 2

Ajoutez une contrainte de contrôle comme ceci. La différence est que vous retournerez false si Status = 1 et Count > 0.

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;

Answer 3

Vous pourriez déplacer les enregistrements supprimés vers une table dépourvue de la contrainte, et peut-être utiliser une vue avec UNION des deux tables pour préserver l'apparence d'une seule table.

Answer 4

Vous pourriez utiliser un UNIQUE si vous utilisez NULL comme votre statut supprimé.

Plusieurs lignes avec NULL sont autorisées par un UNIQUE (du moins en SQL standard - je n'ai pas confirmé cela par un test contre SQL Server 2005).

Answer 5

Vous pouvez le faire d'une manière très peu pratique...

Créez une vue schemabound sur votre table.

CREATE VIEW Whatever SELECT * FROM Table Où RecordStatus = 1

Créez maintenant une contrainte unique sur la vue avec les champs que vous voulez.

Une remarque sur les vues liées à des schémas : si vous modifiez les tables sous-jacentes, vous devrez recréer la vue. Il y a beaucoup de problèmes à cause de cela.