为两列的组合添加惟一约束

I have a table and, somehow, the same person got into my Person table 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.

我有一张桌子，不知何故，同一个人两次进入我的个人桌子。现在，主键只是一个自动编号，但是还有另外两个字段，我想强制它们是唯一的。

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)

我只需要一个唯一的person编号和活动= 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中现有表的最佳方式是什么呢?如果其他任何人使用与现有值相同的值进行插入，它就会失败，所以我在应用程序代码中不必担心这个问题。

2 个解决方案

#1

132

Once you have removed your duplicate(s):

一旦你已删除副本:

ALTER TABLE dbo.yourtablename
  ADD CONSTRAINT uq_yourtablename UNIQUE(column1, column2);

或

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.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/

If you want to prevent exceptions from bubbling up to the application, without making changes to the application, you can use an INSTEAD OF trigger:

如果您希望防止异常冒泡到应用程序，而不对应用程序进行更改，您可以使用替代触发器:

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.

但是如果你不告诉用户他们没有执行插入操作，他们会想知道为什么没有数据，也没有例外报告。

EDIT here 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.

Msg 2627，第14层，状态1，第3行违反唯一密钥约束“uq_Person”。不能在对象'dbo.Person'中插入重复键。声明已被终止。

#2

This can also be done in the GUI:

这也可以在GUI中实现:

Under the table "Person", right click Indexes
在表“Person”下，右击索引。
Click/hover New Index
单击/悬停新指数
Click Non-Clustered Index...
单击集群指数……

A default Index name will be given but you may want to change it.
将会给出一个默认的索引名称，但是您可能想要更改它。
Check Unique checkbox
检查独特的复选框
Click Add... button
单击Add……按钮

Check the columns you want included
检查要包含的列

Click OK in each window.
在每个窗口中单击OK。

#1

132