Oracle主键约束、唯一键约束、唯一索引的区别
收藏

点击蓝字“程序员考拉”欢迎关注!


一般,我们看到术语“索引”和“键”交换使用,但实际上这两个是不同的。索引是存储在数据库中的一个物理结构,键纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。索引和键的混淆通常是由于数据库使用索引来实施完整性约束。

 

接下来我们看看数据库中的主键约束、唯一键约束和唯一索引的区别。

SQL> select * from v$version; BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE    11.2.0.1.0      ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - Production


 

SQL> create table test (          2  id int,3  name varchar2(20),4  constraint pk_test primary key(id))5  tablespace users; Table created.


SQL> select constraint_name, constraint_type from user_constraints;

 

CONSTRAINT_NAME                C------------------------------ -PK_TEST                        P

在test表中,我们指定了ID列作为主键,Oracle数据库会自动创建一个同名的唯一索引:

SQL> select index_name, index_type, uniqueness, tablespace_name2  from user_indexes3  where table_owner='SCOTT'4  and table_name = 'TEST'; INDEX_NAME           INDEX_TYPE           UNIQUENES TABLESPACE_NAME-------------------- -------------------- --------- ------------------------------PK_TEST              NORMAL               UNIQUE    USERS

此时,如果我们再试图在ID列上创建一个唯一索引,Oracle会报错,因为该列上已经存在一个唯一索引:

SQL> create unique index idx_test_uk on test(id);create unique index idx_test_uk on test(id)                                        *ERROR at line 1:ORA-01408: such column list already indexed

即使创建非唯一索引也不行:

SQL> create index idx_test_id on test(id);create index idx_test_id on test(id)                                 *ERROR at line 1:ORA-01408: such column list already indexed

那么唯一键约束的情况是怎样的呢?

SQL> drop table test purge; Table dropped.

 

SQL> create table test(2  id int,3  name varchar2(20),4  constraint uk_test unique(id)); Table created.

 

SQL> select constraint_name, constraint_type from user_constraints; CONSTRAINT_NAME                C------------------------------ -UK_TEST                        U

查看此时的索引情况:

SQL> select index_name, index_type, uniqueness, tablespace_name2  from user_indexes3  where table_owner='SCOTT'4  and table_name = 'TEST'; INDEX_NAME           INDEX_TYPE           UNIQUENES TABLESPACE_NAME-------------------- -------------------- --------- ------------------------------UK_TEST              NORMAL               UNIQUE    USERS

Oracle同样自动创建了一个同名的唯一索引,而且也不允许再在此列上创建唯一索引或非唯一索引。

 

我们知道,主键约束要求列值非空(NOT NULL),那么唯一键约束是否也要求非空呢?

SQL> insert into test values(1, 'Sally');1 row created.SQL> insert into test values(null, 'Tony');1 row created.SQL> insert into test values(null, 'Jack');1 row created.SQL> select * from test;        ID NAME---------- --------------------1 Sally           Tony           Jack

从实验结果来看,唯一键约束并没有非空要求。

 

接下来我们看看唯一索引对列值的非空要求有什么不同。

SQL> drop table test purge;Table dropped.
SQL> create table test( 2 id int,  3  name varchar2(20));Table created.
SQL> create unique index idx_test_id on test (id);Index created. SQL> insert into test values(1, 'Sally');1 row created. SQL> insert into test values(null, 'Tony');1 row created. SQL> insert into test values(null, 'Jack');1 row created. SQL> select * from test; ID NAME---------- -------------------- 1 Sally Tony Jack

通过实验,我们看出唯一索引与唯一键约束一样对列值非空不做要求。

 

如果我们让主键约束或者唯一键约束失效,Oracle自动创建的唯一索引是否会受到影响?

SQL> drop table test purge;Table dropped. SQL> create table test(  2  id int,  3  name varchar2(20),  4  constraint uk_test unique(id)); Table created. SQL> select index_name, index_type, uniqueness from user_indexes; INDEX_NAME                     INDEX_TYPE                  UNIQUENES------------------------------ --------------------------- ---------UK_TEST                        NORMAL                      UNIQUE SQL> alter table test disable constraint uk_test;Table altered. SQL> select index_name, index_type, uniqueness from user_indexes;no rows selected

当主键约束或者唯一键约束失效时,Oracle会删除隐式创建的唯一索引。

 

如果我们先创建唯一索引,再创建主键或者唯一键约束,情况又会怎样呢?

SQL> drop table test purge;Table dropped. SQL> create table test(2  id int,  3  name varchar2(20));Table created. SQL> create unique index idx_test_id on test (id); Index created. SQL> select index_name, index_type, uniqueness2  from user_indexes3  where table_owner = 'SCOTT'  4  and table_name = 'TEST';INDEX_NAME                     INDEX_TYPE                  UNIQUENES------------------------------ --------------------------- ---------IDX_TEST_ID                    NORMAL                      UNIQUE SQL> alter table test add constraint uk_test unique (id);Table altered. SQL> select index_name, index_type, uniqueness2  from user_indexes3  where table_owner = 'SCOTT'  4  and table_name = 'TEST';INDEX_NAME                     INDEX_TYPE                  UNIQUENES------------------------------ --------------------------- ---------IDX_TEST_ID                    NORMAL                      UNIQUE SQL> select constraint_name, constraint_type2  from user_constraints3  where table_name = 'TEST'; CONSTRAINT_NAME                C------------------------------ -UK_TEST                        U SQL> alter table test disable constraint uk_test;Table altered. SQL> select constraint_name, constraint_type, status2  from user_constraints  3  where table_name = 'TEST';CONSTRAINT_NAME                C STATUS------------------------------ - --------UK_TEST                        U DISABLED SQL> select index_name, index_type, uniqueness, status2  from user_indexes3  where table_owner = 'SCOTT'  4  and table_name = 'TEST';INDEX_NAME                     INDEX_TYPE                  UNIQUENES STATUS------------------------------ --------------------------- --------- --------IDX_TEST_ID                    NORMAL                      UNIQUE    VALID

实验结果表明,先创建的唯一索引不受约束失效的影响。

 

总结如下:

(1)主键约束和唯一键约束均会隐式创建同名的唯一索引,当主键约束或者唯一键约束失效时,隐式创建的唯一索引会被删除;

(2)主键约束要求列值非空,而唯一键约束和唯一索引不要求列值非空;

(3)相同字段序列不允许重复创建索引;

 ( 4 )创建唯一索引保证了往表中插入重复索引列值的操作都会失败。如果一个单独的sql语句试图往表中插入包含重复索引列值的数据行,sql server将不会插入以上所有行。例如,当一个insert操作试图把从表A中取出的20行插入到表B,而其中的10行跟索引列值重复的话,默认情况下以上20行都不会被插入。然而,如果把索引对应的 “忽略重复键”开关打开的话,包含重复数值的行不会被插入,而非重复数值行会被插入。也就是说,其中的10行会被插入。


约束则没有这一开关,因此定义了一个约束之后,只要有与定义列重复值的行,插入都将被拒绝。


转自:

http://blog.sina.com.cn/s/blog_84509b760102x2rn.html




官方公众号