如何在SQL Server中创建多列唯一约束 [英] How do I create a multiple column unique constraint in 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,
    ...
)

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

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

我想要做的是防止出现同样的Email和DataSetId组合表或另一种方式，Email值对于给定的DataSetId必须是唯一的。

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.

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

I tried creating a unique index on the columns

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

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

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

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

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

推荐答案

但是我发现这对搜索次数
（当搜索电子邮件地址例如

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

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

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:

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

此索引将服务器作为唯一约束强制执行和快速搜索电子邮件。虽然这个索引不能用于快速搜索特定的 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.

如果你定义一个多键索引，它只能按照键的顺序用于搜索。 （A，B，C）上的索引可用于在列 A 上查找值，以便在 A 和 B 或搜索所有三列 A ， B 和 C 。但是，它不能用于单独搜索 B 或 C 上的值。

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.

这篇关于如何在SQL Server中创建多列唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！