条件唯一约束 [英] conditional unique constraint

问题描述

我有一种情况，我需要对一组列强制执行唯一约束，但仅对列的一个值执行.

I have a situation where i need to enforce a unique constraint on a set of columns, but only for one value of a column.

例如，我有一个像 Table(ID, Name, RecordStatus) 这样的表.

So for example I have a table like Table(ID, Name, RecordStatus).

RecordStatus 的值只能为 1 或 2(活动或删除)，我想仅在 RecordStatus = 1 时对 (ID, RecordStatus) 创建唯一约束，因为我不在乎是否有多个已删除的记录同一个ID.

RecordStatus can only have a value 1 or 2 (active or deleted), and I want to create a unique constraint on (ID, RecordStatus) only when RecordStatus = 1, since I don't care if there are multiple deleted records with the same ID.

除了编写触发器，我还能这样做吗?

Apart from writing triggers, can I do that?

我使用的是 SQL Server 2005.

I am using SQL Server 2005.

推荐答案

添加这样的检查约束.不同之处在于，如果 Status = 1 且 Count > 0，您将返回 false.

Add a check constraint like this. The difference is, you'll return false if Status = 1 and 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;

这篇关于条件唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！