SQL 为两列的组合添加唯一约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15800250/
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
Add unique constraint to combination of two columns
提问by leora
I have a table and, somehow, the same person got into my Persontable twice. Right now, the primary key is just an autonumber but there are two other fields that exist that I want to force to be unique.
我有一张桌子，不知何故，同一个人Person两次进入我的桌子。现在，主键只是一个自动编号，但还有两个其他字段我想强制使其唯一。
For example, the fields are:
例如，这些字段是：
ID
Name
Active
PersonNumber
I only want 1 record with a unique PersonNumber and Active = 1.
(So the combination of the two fields needs to be unique)
我只想要 1 个具有唯一 PersonNumber 和 Active = 1 的记录。
（所以这两个字段的组合需要是唯一的）
What is the best way on an existing table in SQL server I can make it so if anyone else does an insert with the same value as an existing value, it fails so I don't have to worry about this in my application code.
SQL Server 中现有表的最佳方法是什么我可以做到，所以如果其他人使用与现有值相同的值进行插入，它会失败，所以我不必在我的应用程序代码中担心这一点。
回答by Aaron Bertrand
Once you have removed your duplicate(s):
删除重复项后：
ALTER TABLE dbo.yourtablename
ADD CONSTRAINT uq_yourtablename UNIQUE(column1, column2);
or
或者
CREATE UNIQUE INDEX uq_yourtablename
ON dbo.yourtablename(column1, column2);
Of course, it can often be better to check for this violation first, before just letting SQL Server try to insert the row and returning an exception (exceptions are expensive).
当然，在让 SQL Server 尝试插入行并返回异常（异常代价高昂）之前，最好先检查此冲突。
http://www.sqlperformance.com/2012/08/t-sql-queries/error-handling
http://www.sqlperformance.com/2012/08/t-sql-queries/error-handling
If you want to prevent exceptions from bubbling up to the application, without making changes to the application, you can use an INSTEAD OFtrigger:
如果您想防止异常冒泡到应用程序，而不对应用程序进行更改，您可以使用INSTEAD OF触发器：
CREATE TRIGGER dbo.BlockDuplicatesYourTable
ON dbo.YourTable
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT 1 FROM inserted AS i
INNER JOIN dbo.YourTable AS t
ON i.column1 = t.column1
AND i.column2 = t.column2
)
BEGIN
INSERT dbo.YourTable(column1, column2, ...)
SELECT column1, column2, ... FROM inserted;
END
ELSE
BEGIN
PRINT 'Did nothing.';
END
END
GO
But if you don't tell the user they didn't perform the insert, they're going to wonder why the data isn't there and no exception was reported.
但是如果你不告诉用户他们没有执行插入，他们会想知道为什么数据不在那里并且没有报告异常。
EDIThere is an example that does exactly what you're asking for, even using the same names as your question, and proves it. You should try it out before assuming the above ideas only treat one column or the other as opposed to the combination...
编辑这里是一个示例，它完全符合您的要求，甚至使用与您的问题相同的名称，并证明了这一点。在假设上述想法只处理一列或另一列而不是组合之前，您应该尝试一下......
USE tempdb;
GO
CREATE TABLE dbo.Person
(
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(32),
Active BIT,
PersonNumber INT
);
GO
ALTER TABLE dbo.Person
ADD CONSTRAINT uq_Person UNIQUE(PersonNumber, Active);
GO
-- succeeds:
INSERT dbo.Person(Name, Active, PersonNumber)
VALUES(N'foo', 1, 22);
GO
-- succeeds:
INSERT dbo.Person(Name, Active, PersonNumber)
VALUES(N'foo', 0, 22);
GO
-- fails:
INSERT dbo.Person(Name, Active, PersonNumber)
VALUES(N'foo', 1, 22);
GO
Data in the table after all of this:
所有这些之后表中的数据：
ID Name Active PersonNumber
---- ------ ------ ------------
1 foo 1 22
2 foo 0 22
Error message on the last insert:
最后一次插入的错误消息：
Msg 2627, Level 14, State 1, Line 3 Violation of UNIQUE KEY constraint 'uq_Person'. Cannot insert duplicate key in object 'dbo.Person'. The statement has been terminated.
消息 2627，级别 14，状态 1，第 3 行 违反唯一键约束“uq_Person”。无法在对象“dbo.Person”中插入重复键。该语句已终止。
回答by Tony L.
This can also be done in the GUI:
这也可以在 GUI 中完成：
- Under the table "Person", right click Indexes
- Click/hover New Index
- Click Non-Clustered Index...
- 在表“人”下，右键单击索引
- 单击/悬停新索引
- 单击非聚集索引...
- A default Index namewill be given but you may want to change it.
- Check Uniquecheckbox
- Click Add...button
- 将给出默认索引名称，但您可能需要更改它。
- 选中唯一复选框
- 单击添加...按钮
- Check the columns you want included
- 检查要包含的列
- Click OKin each window.
- 在每个窗口中单击确定。
回答by user3816689
In my case, I needed to allow many inactives and only one combination of two keys active, like this:
就我而言，我需要允许许多非活动键，并且只允许两个键的一个组合处于活动状态，如下所示：
UUL_USR_IDF UUL_UND_IDF UUL_ATUAL
137 18 0
137 19 0
137 20 1
137 21 0
This seems to work:
这似乎有效：
CREATE UNIQUE NONCLUSTERED INDEX UQ_USR_UND_UUL_USR_IDF_UUL_ATUAL
ON USER_UND(UUL_USR_IDF, UUL_ATUAL)
WHERE UUL_ATUAL = 1;
Here are my test cases:
这是我的测试用例：
SELECT * FROM USER_UND WHERE UUL_USR_IDF = 137
insert into USER_UND values (137, 22, 1) --I CAN NOT => Cannot insert duplicate key row in object 'dbo.USER_UND' with unique index 'UQ_USR_UND_UUL_USR_IDF_UUL_ATUAL'. The duplicate key value is (137, 1).
insert into USER_UND values (137, 23, 0) --I CAN
insert into USER_UND values (137, 24, 0) --I CAN
DELETE FROM USER_UND WHERE UUL_USR_ID = 137
insert into USER_UND values (137, 22, 1) --I CAN
insert into USER_UND values (137, 27, 1) --I CAN NOT => Cannot insert duplicate key row in object 'dbo.USER_UND' with unique index 'UQ_USR_UND_UUL_USR_IDF_UUL_ATUAL'. The duplicate key value is (137, 1).
insert into USER_UND values (137, 28, 0) --I CAN
insert into USER_UND values (137, 29, 0) --I CAN
回答by Diego Venancio
And if you have lot insert queries but not wanna ger a ERROR message everytime , you can do it:
如果您有很多插入查询但不想每次都收到错误消息，您可以这样做：
CREATE UNIQUE NONCLUSTERED INDEX SK01 ON dbo.Person(ID,Name,Active,PersonNumber)
WITH(IGNORE_DUP_KEY = ON)
