ORACLE中主键约束跟唯一索引的区别

1、  分别用两种方法创建主键

create table test1(id number,name varchar2(10));

insert into test1 values(1,'t1');

insert into test1 values(2,'t2');

commit;

alter table test1 add constraint pk_test1  primary key(id);

 

create table test2(id number,name varchar2(10));

insert into test2 values(3,'t1');

insert into test2 values(4,'t2');

commit;

create unique index pk_test2

on  test2(id);

alter table test2 add constraint pk_test2 primary key(id);

 

1）  查看约束及索引

select table_name,constraint_name,constraint_type,index_name from user_constraints uc where uc.table_name in ('TEST1','TEST2');

 

TEST1  PK_TEST1        P          PK_TEST1

TEST2  PK_TEST2        P          PK_TEST2

 

证明目前两个表上都有一个主键约束，而且都有一个索引。

 

select table_name,index_name,index_type,uniqueness from user_indexes ui where ui.table_name in ('TEST1','TEST2');

 

TEST1  PK_TEST1        NORMAL         UNIQUE

TEST2  PK_TEST2        NORMAL         UNIQUE

 

两个索引也都是唯一索引。

 

此时似乎很难区分两个索引的区别。

2、区别：

1）高可用分析：第一种情况下是Oracle在创建主键约束的自动创建索引，后一种是手动创建索引，然后再基于此唯一索引创建主键约束。在并发事务量较高的情况下，后一种情况可以先以online方式创建索引，减少表的读写阻塞。

 

2）  删除方式不同:第一种情况可通过删除约束进而删除索引。

alter table test1 drop constraint pk_test1;

alter table test2 drop constraint pk_test2;

 

select table_name,index_name,index_type,uniqueness 

from user_indexes ui where ui.table_name in ('TEST1','TEST2');

 

1  TEST2 PK_TEST2    NORMAL      UNIQUE

 

发现删除约束并不能删除通过第二种方式建的索引，Oracle提供添加drop index。

alter table test2 drop constraint pk_test2 drop index

 

3)区分存在的主键的创建方式

   可通过查询sys.ind$视图的PROPERTY字段获得，此字段定义可通过$ORACLE_HOME/rdbms/admin/sql.bsp

        property      number not null,    /* immutable flags for life of the index */

                                                            /* unique : 0x01 */

                                                       /* partitioned : 0x02 */

                                                           /* reverse : 0x04 */

                                                        /* compressed : 0x08 */

                                                        /* functional : 0x10 */

                                              /* temporary table index: 0x20 */

                         /* session-specific temporary table index: 0x40 */

                                              /* index on embedded adt: 0x80 */

                     /* user said to check max length at runtime: 0x0100 */

                                              /* domain index on IOT: 0x0200 */

                                                      /* join index : 0x0400 */

           /* functional index expr contains a PL/SQL function : 0x0800 */

                        /* The index was created by a constraint : 0x1000 */

                           /* The index was created by create MV : 0x2000 */

 

索引类型分别用对应的16进制来表示，而property存储的是十进制，可通过进制转换获得索引的真正类型。

 

select si.PROPERTY,ui.index_name

from sys.ind$ si,user_indexes ui,user_objects uo

where si.obj#=uo.OBJECT_ID

and ui.index_name=uo.OBJECT_NAME

and ui.index_name in ('PK_TEST1','PK_TEST2')

 

PK_TEST1    4097

PK_TEST2    1

 

 

Pk_test1对应的16进制为1001，有1000+1,即索引性质：unique+ The index was created by a constraint,而PK_test2则为unique索引。