如何在SQL Server中创建多列唯一约束

I have a table that contains, for example, two fields that I want to make unique within the database. For example:

我有一个表，包含，例如，我想在数据库中使两个字段唯一。例如：

create table Subscriber (
    ID int not null,
    DataSetId int not null,
    Email nvarchar(100) not null,
    ...
)

The ID column is the primary key and both DataSetId and Email are indexed.

ID列是主键，DataSetId和Email都被编入索引。

What I want to be able to do is prevent the same Email and DataSetId combination appearing in the table or, to put it another way, the Email value must be unique for a given DataSetId.

我希望能够做的是阻止表中出现相同的Email和DataSetId组合，换句话说，对于给定的DataSetId，Email值必须是唯一的。

I tried creating a unique index on the columns

我尝试在列上创建一个唯一索引

CREATE UNIQUE NONCLUSTERED INDEX IX_Subscriber_Email
ON Subscriber (DataSetId, Email)

but I found that this had quite a significant impact on search times (when searching for an email address for example - there are 1.5 million rows in the table).

但我发现这对搜索时间产生了相当大的影响（例如，当搜索电子邮件地址时 - 表中有150万行）。

Is there a more efficient way of achieving this type of constraint?

有没有更有效的方法来实现这种类型的约束？

2 个解决方案

#1

but I found that this had quite a significant impact on search times (when searching for an email address for example

但我发现这对搜索时间有很大影响（例如搜索电子邮件地址时）

The index you defined on (DataSetId, Email) cannot be used for searches based on email. If you would create an index with the Email field at the leftmost position, it could be used:

您在（DataSetId，Email）上定义的索引不能用于基于电子邮件的搜索。如果要在最左边的位置创建一个带有“电子邮件”字段的索引，则可以使用它：

CREATE UNIQUE NONCLUSTERED INDEX IX_Subscriber_Email
   ON Subscriber (Email, DataSetId);

This index would server both as a unique constraint enforcement and as a means to quickly search for an email. This index though cannot be used to quickly search for a specific DataSetId.

该索引既可以作为唯一约束强制执行，也可以作为快速搜索电子邮件的方法。此索引虽然不能用于快速搜索特定的DataSetId。

The gist of it if is that whenever you define a multikey index, it can be used only for searches in the order of the keys. An index on (A, B, C) can be used to seek values on column A, for searching values on both A and B or to search values on all three columns A, B and C. However it cannot be used to search values on B or on C alone.

它的要点是，无论何时定义多键索引，它都只能用于按键顺序搜索。（A，B，C）上的索引可用于在列A上搜索值，用于搜索A和B上的值或搜索所有三个列A，B和C上的值。但是它不能用于搜索值在B或C单独。

#2

-1

I assume that only way to enter data into that table is through SPs, If that's the case you can implement some logic in your insert and update SPs to find if the values you are going to insert / update is already exists in that table or not.

我假设只有通过SP将数据输入到该表中的方法，如果是这种情况，您可以在插入中实现一些逻辑并更新SP以查找您要插入/更新的值是否已存在于该表中。

Something like this

像这样的东西

create proc spInsert
(
    @DataSetId int,
    @Email nvarchar(100)
)
as
begin

if exists (select * from tabaleName where DataSetId = @DataSetId and Email = @Email)
    select -1 -- Duplicacy flag
else
begin
    -- insert logic here
    select 1 -- success flag
end

end
GO


create proc spUpdate
(
   @ID int,
   @DataSetId int,
   @Email nvarchar(100)
)
as
begin

if exists 
(select * from tabaleName where DataSetId = @DataSetId and Email = @Email and ID <> @ID)
    select -1 -- Duplicacy flag
else
begin
    -- insert logic here
    select 1 -- success flag
end

end
GO

#1