约束和索引，是我们在做数据库设计时，经常用到的两个对象类型，常见的约束分为5种：主键、外键、唯一、CHECK、NOT NULL，常见索引有2种：普通索引、唯一索引。这里我们只讨论唯一约束和唯一索引的差异。

唯一约束和唯一索引都有一个相同功能，那就是在指定的表中，某一列或多列的数据的不能重复，必须唯一存在。

约束主要强调数据的业务规则，而索引存在的目的纯粹是为了提高查询的性能。

一张表只能有一个主键，但可以有多个候选键，候选键在做实际数据库设计时，都会转换成唯一约束，

下面我们将通过几个示例，来观察一下这两种对象有何差异。

1、 建立一张测试表，并提供部分测试数据。

定义了6列，并写入1000行数据，各列的数据都是1-1000的自然数序。

SQL> 
SQL> create table t1(a1, a2, b1, b2, c1,c2)
  2  as
  3  select rownum, rownum, rownum, rownum, rownum, rownum
  4  from dual connect by rownum <= 1000;
Table created

SQL>

2、 按常规的操作，分别建立唯一约束和唯一索引

SQL> alter table t1 add constraint uix_t1_c1 unique(c1);
Table altered
SQL> create unique index ix_t1_d1 on t1(d1);
Index created

这两个对象创建完成后，我们查询一下数据字典。

SQL> col object_type format a15;
SQL> col object_name format a15;
SQL> col UNIQUENESS format a15;
SQL> col index_type format a15;
SQL> select index_name object_name , index_type, UNIQUENESS, 'INDEX' object_type
  2  from user_indexes
  3  where index_name in (upper('uix_t1_a1'),upper('ix_t1_a2'))
  4  union all
  5  select constraint_name, constraint_type, 'CON', 'CON' object_type
  6  from user_constraints
  7  where constraint_name in (upper('uix_t1_a1'),upper('ix_t1_a2'));
OBJECT_NAME     INDEX_TYPE      UNIQUENESS      OBJECT_TYPE
--------------- --------------- --------------- ---------------
IX_T1_A2        NORMAL          UNIQUE          INDEX
UIX_T1_A1       NORMAL          UNIQUE          INDEX
UIX_T1_A1       U               CON             CON

SQL>

可以看到，UIX_T1_A1 除了创建了一个约束外（第3行），还创建了一个同名的索引（第2行），而IX_T1_A2 这个索引，仅仅是一个索引。

3、 函数索引

唯一约束创建时，只能把具体的列进行操作，而索引却可以对列进行计算再创建索引，即函数索引。

SQL> alter table t1 add constraint uix_t1_b1 unique(b1 + 100);
alter table t1 add constraint uix_t1_b1 unique(b1 + 100)
ORA-00904: : 标识符无效
SQL> create unique index ix_t1_b2 on t1(b2 + 100);
Index created

SQL>

4、 条件索引（仅SQL Server支持）

在 SQL Server 数据库，可以在创建索引时使用 where 子句，以减少索引的大小，提高检索的性能，而约束却没有这样的功能。

create table test(id int, status int)
go
create unique index ix_test_id on test(id) where status = 1
go
Package created

5、 相同之处

都可以做主键表，被其他表参考；

所使用的列对是否可空没有限制；

6、 总结

在数据库中，每种对象设计都有各自考量，当我们从根本上了解各种对象的特性，才能从细节做好的设计。

打赏 支付宝打赏 微信打赏