主键约束与其索引的问题

56 阅读 0 评论 37 点赞

我是靠谱客的博主干净菠萝，最近开发中收集的这篇文章主要介绍主键约束与其索引的问题，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

结论：

1）对于Oracle自动（递归）创建出来的唯一索引，在进行约束（唯一约束、主键约束）删除的时候，Oracle会级联把索引也删除。特别需要注意
必须满足两个条件，1）索引必须是唯一 2）必须是Oracle自动创建。上面的例子5里，虽然Oracle也认为是自动创建的，但是由于不是唯一索引，因此也不会被Oracle级联删除。

2）对于我们手工创建的索引，在进行约束（唯一约束、主键约束）删除的时候，由于不是Oracle自动创建的，因此Oracle会保留索引。

由于property值都是由2的倍数值的和组成的，那么一个简单的判定是不是满足递归删除索引的公式就是：
bitand(ind$.property,4097) = 4097

1、如果索引是单独创建，那么在删除主键约束的时候，索引不会删除，除非使用alter table t drop constraint pk_t cascade drop index;

15:59:16 sys@CRMUAT>drop table t purge;
Table dropped.
16:05:20 sys@CRMUAT>create table t (x int, y int);
Table created.
16:05:28 sys@CRMUAT> create unique index pk_t on t (x);
Index created.
16:05:33 sys@CRMUAT>alter table t add (constraint pk_t primary key(x));
Table altered.
16:05:39 sys@CRMUAT>select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from user_constraints where table_name = 'T';
OWNER CONSTRAINT_NAME
C TABLE_NAME INDEX_OWNER
INDEX_NAME
----- ------------------------------ - ---------- ------------ ----------
SYS
PK_T
P T
SYS
PK_T
16:05:46 sys@CRMUAT> select INDEX_NAME,INDEX_TYPE,GENERATED from user_indexes where table_name = 'T';
INDEX_NAME INDEX_TYPE
G
---------- --------------------------- -
PK_T
NORMAL
N
16:05:52 sys@CRMUAT>alter table t drop constraint pk_t cascade;
Table altered.
16:05:59 sys@CRMUAT> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from user_constraints where table_name = 'T';
no rows selected
16:06:05 sys@CRMUAT> select INDEX_NAME,INDEX_TYPE,GENERATED from user_indexes where table_name = 'T';
INDEX_NAME INDEX_TYPE
G
---------- --------------------------- -
PK_T
NORMAL
N

2、如果索引是在创建主键约束时自动创建，那么在删除主键约束时，索引也会删除

15:54:09 sys@CRMUAT>create table t (x int, y int);
Table created.
15:54:58 sys@CRMUAT>alter table t add (constraint pk_t primary key(x));
Table altered.
15:55:04 sys@CRMUAT>select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_OWNER,INDEX_NAME from user_constraints where table_name = 'T';
OWNER CONSTRAINT_NAME
C TABLE_NAME INDEX_OWNER
INDEX_NAME
----- ------------------------------ - ---------- ------------ ----------
SYS
PK_T
P T
SYS
PK_T
15:55:40 sys@CRMUAT> select INDEX_NAME,INDEX_TYPE,GENERATED from user_indexes where table_name = 'T';
INDEX_NAME INDEX_TYPE                  G
---------- --------------------------- -
PK_T       NORMAL                      N
15:55:48 sys@CRMUAT> alter table t drop constraint pk_t cascade;
Table altered.
15:56:02 sys@CRMUAT>select INDEX_NAME,INDEX_TYPE,GENERATED from user_indexes where table_name = 'T';
no rows selected