SQL 条件唯一约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/866061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
conditional unique constraint
提问by np-hard
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.
我有一种情况，我需要对一组列强制执行唯一约束，但仅针对列的一个值。
So for example I have a table like Table(ID, Name, RecordStatus).
例如，我有一个像 Table(ID, Name, RecordStatus) 这样的表。
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.
RecordStatus 的值只能为 1 或 2（活动或已删除），我想仅在 RecordStatus = 1 时对 (ID, RecordStatus) 创建唯一约束，因为我不在乎是否有多个删除的记录相同ID。
Apart from writing triggers, can I do that?
除了编写触发器，我还能这样做吗？
I am using SQL Server 2005.
我正在使用 SQL Server 2005。
采纳答案by D. Patrick
Add a check constraint like this. The difference is, you'll return false if Status = 1 and Count > 0.
添加这样的检查约束。不同之处在于，如果 Status = 1 且 Count > 0，您将返回 false。
http://msdn.microsoft.com/en-us/library/ms188258.aspx
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;
回答by canon
Behold, the filtered index. From the documentation (emphasis mine):
看，过滤后的索引。从文档（强调我的）：
A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table.A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.
过滤索引是一种优化的非聚集索引，特别适用于涵盖从明确定义的数据子集中进行选择的查询。它使用过滤谓词来索引表中的一部分行。与全表索引相比，设计良好的过滤索引可以提高查询性能并减少索引维护和存储成本。
And here's an example combining a unique index with a filter predicate:
这是一个将唯一索引与过滤谓词组合在一起的示例：
create unique index MyIndex
on MyTable(ID)
where RecordStatus = 1;This essentially enforces uniqueness of IDwhen RecordStatusis 1.
这实质上强制了IDwhen RecordStatusis 的唯一性1。
Note: the filtered index was introduced in SQL Server 2008. For earlier versions of SQL Server, please see this answer.
注意：过滤索引是在 SQL Server 2008 中引入的。对于 SQL Server 的早期版本，请参阅此答案。
回答by Carl Manaster
You could move the deleted records to a table that lacks the constraint, and perhaps use a view with UNION of the two tables to preserve the appearance of a single table.
您可以将已删除的记录移动到缺少约束的表中，并且可能使用带有两个表的 UNION 的视图来保留单个表的外观。
回答by Min
You can do this in a really hacky way...
你可以用一种非常hacky的方式来做到这一点......
Create an schemabound view on your table.
在您的表上创建架构绑定视图。
CREATE VIEW Whatever SELECT * FROM Table WHERE RecordStatus = 1
CREATE VIEW 无论 SELECT * FROM Table WHERE RecordStatus = 1
Now create a unique constraint on the view with the fields you want.
现在使用您想要的字段在视图上创建一个唯一约束。
One note about schemabound views though, if you change the underlying tables you will have to recreate the view. Plenty of gotchas because of that.
但是，关于模式绑定视图的一个注意事项是，如果您更改基础表，则必须重新创建视图。因此有很多问题。
回答by Hobo
If you can't use NULL as a RecordStatus as Bill's suggested, you could combine his idea with a function-based index. Create a function that returns NULL if the RecordStatus is not one of the values you want to consider in your constraint (and the RecordStatus otherwise) and create an index over that.
如果您不能像 Bill 建议的那样使用 NULL 作为 RecordStatus，您可以将他的想法与基于函数的索引结合起来。如果 RecordStatus 不是您要在约束中考虑的值之一（否则为 RecordStatus），创建一个返回 NULL 的函数，并在其上创建索引。
That'll have the advantage that you don't have to explicitly examine other rows in the table in your constraint, which could cause you performance issues.
这样做的好处是您不必显式检查约束中表中的其他行，这可能会导致性能问题。
I should say I don't know SQL server at all, but I have successfully used this approach in Oracle.
我应该说我根本不知道 SQL 服务器，但是我已经在 Oracle 中成功地使用了这种方法。
回答by ichiban
Because, you are going to allow duplicates, a unique constraint will not work. You can create a check constraint for RecordStatus column and a stored procedure for INSERT that checks the existing active records before inserting duplicate IDs.
因为，您将允许重复，唯一约束将不起作用。您可以为 RecordStatus 列创建一个检查约束，并为 INSERT 创建一个存储过程，在插入重复 ID 之前检查现有的活动记录。
