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

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

例如，字段为:

ID
Name
Active
PersonNumber
< p > 我只想要一个独特的PersonNumber和活动= 1的记录。 < br > (所以两个字段的组合需要是唯一的)

在SQL server的现有表上，最好的方法是什么，我可以让它，如果任何人用与现有值相同的值进行插入，它会失败，这样我就不必在我的应用程序代码中担心这个问题。

251157 次浏览
小开
最佳答案

删除副本后:

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

或

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

当然，在SQL Server尝试插入行并返回异常(异常代价很高)之前，最好先检查这种违反。

如果你想防止异常冒泡到应用程序，而不改变应用程序，你可以使用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

但是，如果您不告诉用户他们没有执行插入，他们就会怀疑为什么数据不存在，并且没有报告异常。

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

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 违反UNIQUE KEY约束'uq_Person'。不能在对象'dbo.Person'中插入重复的键。

. {/p> .

我最近也写了一篇关于对两个列无论哪种顺序应用唯一约束的解决方案:

小开

这也可以在GUI中完成:

  1. 在表格“Person”下，右击索引
  2. 点击/悬停新索引
  3. 单击集群指数……

enter image description here

  1. 将会给出一个默认的索引名称，但你可能想要更改它。
  2. 选中独特的复选框
  3. 单击添加……按钮

enter image description here

  1. 检查您希望包含的列

enter image description here

  1. 在每个窗口中单击好吧。
小开

如果你有很多插入查询，但不想每次都发出错误消息，你可以这样做:

CREATE UNIQUE NONCLUSTERED INDEX SK01 ON dbo.Person(ID,Name,Active,PersonNumber)
WITH(IGNORE_DUP_KEY = ON)

enter image description here

小开

在我的情况下，我需要允许许多非活动，只有一个组合的两个键活动，就像这样:

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

提交答案