contrainte unique conditionnelle
J'ai 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 qu'une valeur 1 ou 2 (active ou supprimée), et je veux créer une contrainte unique sur (ID, RecordStatus) uniquement lorsque RecordStatus = 1, car je ne me soucie pas s'il y a plusieurs enregistrements supprimés avec le même ID.
En dehors de l'écriture de Déclencheurs, puis-je faire qui?
J'utilise SQL Server 2005.
6 réponses
Ajoutez une contrainte de vérification comme celle-ci. 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;
Voici, l'index filtré. De la documentation (emphase mine):
Un index filtré est un index non clustered optimisé spécialement adapté pour couvrir les requêtes qui sélectionnent à partir d'un sous-ensemble de données bien défini. , 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 des index par rapport aux index à table complète.
Et voici un exemple combinant un index unique avec un prédicat de filtre:
create unique index [MyIndex]
on [MyTable]([ID])
where [RecordStatus] = 1, essentiellement, Cela impose l'unicité de ID lorsque RecordStatus est 1.
Remarque: L'index filtré a été introduit dans SQL Server 2008. Pour les versions antérieures de SQL Server, veuillez consulter cette réponse .
Vous pouvez déplacer les enregistrements supprimés vers une table qui n'a pas la contrainte, et peut-être utiliser une vue avec L'UNION des deux tables pour préserver l'apparence d'une seule table.
Vous pouvez le faire d'une manière vraiment hacky...
Créez une vue schemabound sur votre table.
Créer une vue SELECT * from Table Où RecordStatus = 1
Créez maintenant une contrainte unique sur la vue avec les champs que vous voulez.
Une note sur les vues schemabound cependant, si vous modifiez les tables sous-jacentes, vous devrez recréer la vue. Beaucoup de pièges à cause de ça.
Parce que, vous allez autoriser les doublons, une contrainte unique ne fonctionnera pas. Vous pouvez créer une colonne check constraint for RecordStatus et une procédure stockée pour INSERT qui vérifie les enregistrements actifs existants avant d'insérer des ID en double.
Si vous ne pouvez pas utiliser NULL comme RecordStatus comme suggéré par Bill, vous pouvez combiner son idée avec un index basé sur la fonction. Créez une fonction qui renvoie NULL si RecordStatus n'est pas l'une des valeurs que vous souhaitez prendre en compte dans votre contrainte (et RecordStatus sinon) et créez un index sur cela.
Cela aura l'avantage de ne pas avoir à examiner explicitement les autres lignes de la table dans votre contrainte, ce qui pourrait vous causer des problèmes de performances.
Je devrais disons que je ne connais pas du tout SQL server, mais j'ai utilisé avec succès cette approche dans Oracle.