为两列的组合添加唯一约束 [英] Add unique constraint to combination of two columns

问题描述

我有一张桌子，不知何故，同一个人两次进入了我的 Person 桌子.现在，主键只是一个自动编号，但还有两个其他字段我想强制使其唯一.

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.

例如，字段是:

ID  
Name  
Active  
PersonNumber  

我只想要 1 个具有唯一 PersonNumber 且 Active = 1 的记录.
(所以两个字段的组合需要唯一)

I only want 1 record with a unique PersonNumber and Active = 1.
(So the combination of the two fields needs to be unique)

SQL Server 中现有表的最佳方法是什么我可以做到，所以如果其他人使用与现有值相同的值进行插入，它会失败，所以我不必在我的应用程序中担心这个代码.

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.

推荐答案

删除重复项后:

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

或

CREATE UNIQUE INDEX uq_yourtablename
  ON dbo.yourtablename(column1, column2);

当然，在让 SQL Server 尝试插入行并返回异常之前，先检查此违规通常会更好(异常很昂贵).

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).

http://www.sqlperformance.com/2012/08/t-sql-查询/错误处理

http://www.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/

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

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 这里是一个完全符合您要求的示例，甚至使用与您的问题相同的名称，并证明了这一点.在假设上述想法只处理一列或另一列而不是组合之前，您应该尝试一下...

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:

消息 2627，级别 14，状态 1，第 3 行违反 UNIQUE KEY 约束uq_Person".无法在对象dbo.Person"中插入重复键.声明已终止.

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.

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