我有这样一种情况，需要对一组列强制执行唯一约束，但仅针对某一列的一个值。
例如，我有一个表Table(ID, Name, RecordStatus)。
RecordStatus 只能是 1 或 2 (表示活动或删除)，当 RecordStatus = 1 时，我想在 (ID, RecordStatus) 上创建一个唯一约束，因为如果有多个已删除的记录具有相同的 ID ，我并不在意。
除了编写触发器，我还能做到吗？
我正在使用 SQL Server 2005。
条件唯一约束
115
- np-hard
2
1这种设计常常会带来烦恼。您是否考虑过更改设计，使得名义上“删除”的记录从表中物理删除，并可能移动到“归档”表中？ - onedaywhen
1因为无法编写UNIQUE约束来强制执行简单键应该被视为“代码异味”，在我看来。如果您无法更改设计（SQL DDL），因为许多其他表引用了此表，那么我敢打赌，您的SQL DML也会因此而受到影响，即您必须记住在大多数搜索条件和涉及此表的连接条件中添加...AND Table.RecordStatus = 1'，并在偶尔遗漏时遇到微妙的错误。 - onedaywhen
7个回答
183
看，过滤索引来了。根据文档（重点是我的）：
过滤索引是一种优化的非聚集索引，特别适合于选择来自定义数据子集的查询。 它使用筛选谓词对表中的一部分行进行索引。 设计良好的过滤索引可以提高查询性能，同时与全表索引相比减少索引维护和存储成本。
这里还有一个将唯一索引与筛选谓词相结合的示例：
create <b>unique</b> index MyIndex
on MyTable(ID)
<b>where RecordStatus = 1;</b>当 RecordStatus 为 1 时，这实际上强制要求 ID 的唯一性。
创建该索引后，违反唯一性将引发错误：
Msg 2601, Level 14, State 1, Line 13
Cannot insert duplicate key row in object 'dbo.MyTable' with unique index 'MyIndex'. The duplicate key value is (9999).
注意：过滤索引是在SQL Server 2008中引入的。对于早期版本的SQL Server，请参见此答案。
- canon
1
请注意，SQL Server 对于过滤索引需要
ansi_padding，因此在创建过滤索引之前，请确保执行 SET ANSI_PADDING ON 打开此选项。 - naXa stands with Ukraine43
添加一个类似这样的检查约束。不同之处在于，如果状态为1且计数大于0，则返回false。
http://msdn.microsoft.com/zh-cn/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;
- D. Patrick
7
我查看了表级别的检查约束，但似乎没有办法将插入或更新的值传递给函数，你知道怎么做吗？ - np-hard
好的，我发了一个示例脚本，它将帮助您证明我所说的。我已经测试过了，它是有效的。 如果您查看两个注释行，您将看到我收到的消息。请注意，在我的实现中，我只是确保您不能添加一个与已有活动项相同的第二项。如果已经有一个活动项，则可以修改逻辑，使不能添加任何带有相同 ID 的项目。 有了这种模式，可能性就几乎无限了。 - D. Patrick
3这适用于插入操作，但似乎不适用于更新操作。例如，在其他插入操作之后添加此操作可以正常运行，这出乎我的意料。
将以下内容添加到其他插入语句后：
INSERT INTO CheckConstraint VALUES (1, 'No ProblemsA', 2);
然后执行更新操作：
update CheckConstraint set Recordstatus=1 where name = 'No ProblemsA' - dwidel
@D.Patrick，如何在Oracle上访问
ALTER TABLE中的CheckActiveCountConstraint？ - masT我不确定为什么，但这在 SQL Server 2016 中对我无效。 我不得不添加
OR RecordStatus = 2 以使约束也允许插入状态为2的记录，因此它必须像这样：ALTER TABLE CheckConstraint ADD CONSTRAINT CheckActiveCountConstraint CHECK (NOT (dbo.CheckActiveCount(Id) > 1 AND RecordStatus = 1) OR RecordStatus = 2); ...或许是因为原始示例并没有考虑添加状态为2的记录，而是在某些时候仅从1更新到2。 - gbdavid显示剩余2条评论
13
你可以将已删除的记录移动到一个没有该约束的表中，并且可能会使用一个包含两个表的联合视图来保留单个表的外观。
- Carl Manaster
1
3这实际上非常聪明，卡尔。虽然它不是问题的答案，但这是一个好的解决方案。如果表有很多行，那么查找活动记录也可以加快，因为您可以查看活动记录表。它还可以加快约束，因为唯一约束使用索引，而不是我下面编写的检查约束，后者必须执行计数。我喜欢它。 - D. Patrick
3
您可以用一种非常巧妙的方法来做到这一点...
在您的表上创建一个模式绑定视图。
CREATE VIEW Whatever SELECT * FROM Table WHERE RecordStatus = 1
现在，在您想要的字段上为该视图创建唯一约束。
关于模式绑定视图的一点说明，如果您更改底层表，您将不得不重新创建视图。因此有很多注意事项。
- Min
3
这是一个糟糕的想法。问题并不在于此。 - FabianoLothor
我曾经使用过一次 schemabound 视图，再也没有重复这个错误了。它们可能非常难以处理。问题不在于如果更改底层表格就必须重新创建视图 - 至少在 SQL Server 中，您可能必须为所有视图执行此操作。问题在于您无法更改表格而不首先删除视图，而您可能无法在没有首先删除对其的引用的情况下执行此操作。哦，另外存储可能会有问题 - 要么是因为空间，要么是因为插入和更新时增加的成本。 - MattW
2
对于那些仍在寻找解决方案的人，我发现了一个不错的答案，它回答了类似的问题，我认为这对许多人仍然有用。虽然将已删除记录移动到另一个表可能是更好的解决方案，但对于那些不想移动记录的人，可以使用链接答案中的想法，具体如下：
- 当记录可用/活动时，设置deleted=0。
- 将行标记为删除时，设置deleted=<row_id或其他唯一值>。
- Saumya Kanta Swain
1
如果您不能像Bill建议的那样使用NULL作为RecordStatus，您可以将他的想法与基于函数的索引相结合。创建一个函数，如果RecordStatus不是您要考虑的值之一，则返回NULL（否则返回RecordStatus），并在其上创建索引。
这将具有优势，即您不必明确地检查表中的其他行以满足约束条件，这可能会导致性能问题。
我应该说我完全不了解SQL Server，但我已经成功地在Oracle中使用了这种方法。
- Hobo
1
好主意，但是在SQL Server中没有基于函数的索引。
不过还是谢谢你的回答。 - np-hard
0
因为你要允许重复，所以唯一约束将不起作用。你可以为RecordStatus列创建一个检查约束，并为INSERT创建一个存储过程，在插入重复的ID之前检查现有的活动记录。
- ichiban
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
- 相关问题
- 9 Oracle表达式唯一约束
- 3 日期唯一约束
- 4 唯一约束 vs 唯一索引？
- 22 复合唯一约束SQL
- 46 唯一约束 vs 唯一索引
- 3 无法移除唯一约束
- 292 多列唯一约束
- 3 Postgres 条件唯一约束（无需选择）
- 13 多表唯一约束
- 9 PostgreSQL：唯一约束或唯一索引