当前位置:首页 > Oracle > 正文内容

数据库中唯一约束与唯一索引的区别

管理员2年前 (2018-08-22)Oracle755

约束和索引,是我们在做数据库设计时,经常用到的两个对象类型,常见的约束分为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、 总结

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

打赏 支付宝打赏 微信打赏
    扫描二维码至手机访问

    扫描二维码推送至手机访问。

    版权声明:本文由卖水果的net发布,如需转载请注明出处。

    转载请注明出处:http://www.msgde.net/oracle/oracle_diff_unique_and_constraint.html

    分享给朋友:

    发表评论

    访客

    ◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。