主页 / dba / 问题 / 144
Accepted
bernd_k
Asked: 2011-01-05 02:32:27 +0800 CST

什么时候应该使用唯一约束而不是唯一索引？

  • 772

当我希望一列具有不同的值时，我可以使用约束

create table t1(
id int primary key,
code varchar(10) unique NULL
);
go

或者我可以使用唯一索引

create table t2(
id int primary key,
code varchar(10) NULL
);
go

create unique index I_t2 on t2(code);

具有唯一约束的列似乎是唯一索引的良好候选者。

是否有任何已知的理由使用唯一约束而不是使用唯一索引？

sql-server index

7 个回答

  1. Best Answer

    在引擎盖下，唯一约束的实现方式与唯一索引相同 - 需要索引来有效地满足强制执行约束的要求。即使索引是作为 UNIQUE 约束的结果创建的，如果查询规划器认为它是处理给定查询的最佳方式，它也可以像使用任何其他索引一样使用它。

    因此，对于同时支持这两种功能的数据库，选择使用哪种功能通常归结为首选样式和一致性。

    如果您打算将索引用作索引（即您的代码可能依赖于对该字段进行快速搜索/排序/过滤），我将明确使用唯一索引（并注释源）而不是约束来实现清楚 - 这样，如果在应用程序的后续版本中更改唯一性要求，您（或其他一些编码人员）将知道确保将非唯一索引放置在唯一索引的位置（只需删除唯一约束即可删除索引完全）。此外，可以在索引提示中命名一个特定的索引（即 WITH(INDEX(ix_index_name)），我认为这不是在幕后创建的用于管理唯一性的索引的情况，因为您不太可能知道它的名称。

    同样，如果您只需要将唯一性作为业务规则而不是需要搜索或用于排序的字段，那么我将使用约束，再次使预期用途在其他人查看您的表定义时更加明显。

    请注意，如果您在同一字段上同时使用唯一约束和唯一索引，则数据库将不够亮以查看重复项，因此您最终会得到两个索引，这将消耗额外空间并减慢行插入/更新速度。

    • 207

  2. 除了其他答案中的要点之外，这里还有两者之间的一些关键区别。

    注意：错误消息来自 SQL Server 2012。

    错误

    违反唯一约束返回错误 2627。

    Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'P1U_pk'. Cannot insert duplicate key in object 'dbo.P1U'. The duplicate key value is (1).
The statement has been terminated.

    违反唯一索引会返回错误 2601。

    Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.P1' with unique index 'P1_u'. The duplicate key value is (1).
The statement has been terminated.

    禁用

    不能禁用唯一约束。

    Msg 11415, Level 16, State 1, Line 1
Object 'P1U_pk' cannot be disabled or enabled. This action applies only to foreign key and check constraints.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.

    但是可以禁用主键约束或唯一约束后面的唯一索引，就像任何唯一索引一样。帽尖Brain2000。

    ALTER INDEX P1_u ON dbo.P1 DISABLE ;

    请注意禁用聚集索引会使数据无法访问的常见警告。

    选项

    唯一约束支持 和 之类的索引选项FILLFACTOR，IGNORE_DUP_KEY尽管并非每个版本的 SQL Server 都如此。

    包含的列

    非聚集索引可以包括非索引列（称为覆盖索引，这是一项主要的性能增强）。PRIMARY KEY 和 UNIQUE 约束后面的索引不能包含列。帽子提示@ypercube。

    过滤

    无法过滤唯一约束。

    可以过滤唯一索引。

    CREATE UNIQUE NONCLUSTERED INDEX Students6_DrivesLicence_u 
ON dbo.Students6( DriversLicenceNo ) WHERE DriversLicenceNo is not null ;

    外键约束

    外键约束不能引用过滤的唯一索引，尽管它可以引用非过滤的唯一索引（我认为这是在 SQL Server 2005 中添加的）。

    命名

    创建约束时，指定约束名称是可选的（对于所有五种类型的约束）。如果您不指定名称，那么 MSSQL 将为您生成一个名称。

    CREATE TABLE dbo.T1 (
    TID int not null PRIMARY KEY
) ;
GO
CREATE TABLE dbo.T2 (
    TID int not null CONSTRAINT T2_pk PRIMARY KEY
) ;

    创建索引时，必须指定名称。

    帽子提示@i-one。

    链接

    http://technet.microsoft.com/en-us/library/aa224827(v=SQL.80).aspx

    http://technet.microsoft.com/en-us/library/ms177456.aspx

    • 129

  3. 引用 MSDN 作为权威来源：

    创建 UNIQUE 约束和创建独立于约束的唯一索引之间没有显着差异。数据验证以相同的方式进行，查询优化器不会区分由约束创建的唯一索引或手动创建的唯一索引。但是，在列上创建 UNIQUE 约束会使索引的目标变得清晰......更多信息在这里

    和...

    数据库引擎会自动创建一个 UNIQUE 索引来强制执行 UNIQUE 约束的唯一性要求。因此，如果尝试插入重复行，数据库引擎将返回一条错误消息，指出违反了 UNIQUE 约束并且不会将该行添加到表中。除非明确指定聚集索引，否则默认情况下会创建唯一的非聚集索引以强制执行 UNIQUE 约束...更多信息在这里

    其他进入：https ://technet.microsoft.com/en-us/library/aa224827%28v=sql.80%29.aspx

    • 13

  4. 唯一约束和唯一索引之间的主要区别之一是另一个表上的外键约束可以引用构成唯一约束的列。这不适用于唯一索引。此外，唯一约束被定义为 ANSI 标准的一部分，而索引则不是。最后，唯一约束被认为存在于逻辑数据库设计领域（可能由不同的数据库引擎以不同方式实现），而索引是物理方面。因此，唯一约束更具声明性。在几乎所有情况下，我都更喜欢唯一约束。

    • 7

  5. 在 Oracle 中，一个主要的区别是您可以创建一个函数唯一索引，这在唯一约束下是不可行的：

    例如

    create unique index ux_test on my_table (case when amount != 0 then fk_xyz end);

    所以fk_xyz只有记录才有amount != 0。

    • 0

  6. 在 Oracle 中，您可以将约束设置为DEFERRED，即您可以定义是否应在之前或之后应用唯一性COMMIT

    • 0

  7. UNIQUE 约束优于 UNIQUE 索引。当约束不唯一时，您需要使用常规或非唯一索引。约束也是另一种类型的索引。索引用于更快的访问。

    唯一索引可以有 where 子句。例如，您可以根据日期列为每一年创建索引

    WHERE Sale_Date BETWEEN '2012-01-01' AND '2012-12-31'
    • -4

相关问题