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

Question

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

例如，这些字段是：

ID  
Name  
Active  
PersonNumber  

我只想要一张带有唯一PersonNumber和Active=1的唱片
（因此这两个字段的组合必须是唯一的）

SQL server中现有表的最佳方式是什么？我可以这样做，如果其他任何人使用与现有值相同的值进行插入，则插入失败，因此我不必在应用程序代码中担心这一点。

Answer 1

在我的例子中，我需要允许许多不活动，并且只允许两个键的一个组合处于活动状态，如下所示：

UUL_USR_IDF  UUL_UND_IDF    UUL_ATUAL
137          18             0
137          19             0
137          20             1
137          21             0

这似乎有效：

CREATE UNIQUE NONCLUSTERED INDEX UQ_USR_UND_UUL_USR_IDF_UUL_ATUAL
ON USER_UND(UUL_USR_IDF, UUL_ATUAL)
WHERE UUL_ATUAL = 1;

以下是我的测试用例：

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

Answer 2

这也可以在GUI中完成：

1. 在"Person"表下，右键单击[索引]
2. 点击/悬停新建索引
3. 点击非聚集索引...

Answer 3

删除副本后：

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

或者

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

当然，在让SQL服务器尝试插入行并返回异常（异常代价高昂）之前，首先检查此违规通常会更好。

>

不同错误处理技术对性能的影响

在输入TRY/CATCH之前检查潜在的约束冲突

如果要防止异常冒泡到应用程序中，而不更改应用程序，可以使用而不是触发器：

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

但是如果你不告诉用户他们没有执行插入，他们会想为什么数据不在那里，并且没有报告异常。

编辑这里是一个例子，它完全符合你的要求，甚至使用与你的问题相同的名称，并证明了这一点。你应该先尝试一下，然后再假设上面的想法只处理一个专栏或另一个专栏，而不是组合。。。

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

所有这些之后的表格中的数据：

ID   Name   Active PersonNumber
---- ------ ------ ------------
1    foo    1      22
2    foo    0      22

上次插入时出现错误消息：

Msg 2627，14级，状态1，第3行违反唯一密钥约束“uq_Person”。无法在对象“dbo”中插入重复密钥。“人”。声明已被终止。

最近我还在博客上写了一个解决方案，可以将唯一约束按任意顺序应用于两列：

• 在顺序无关紧要的情况下强制执行唯一约束