删除主键约束时是否删除索引

问题：删除主键时是否会同时自动删除索引？

答案：是否删除索引取决于索引是创建主键时自动创建的，还是创建主键前手工创建的。

如果期望删除主键时，同时删除索引，安全的做法是增加drop index选项。另外，如果为了防止因存在外键引用而删除失败，可以增加cascade选项。

以下内容在PLSQLDeveloper中亲测，为了代码便于阅读放到eclipse中做了格式调整。

测试无drop index/keepindex选项时的情况

手工创建索引，后增加主键

--建表 SQL> drop table test; drop table test ORA-00942: 表或视图不存在 SQL> create table test(ID INTEGER not null); Table created --建主键 SQL> create unique index PK_TEST on TEST(ID); Index created SQL> alter table TEST   2  add CONSTRAINT PK_TEST PRIMARY KEY (ID) using index; Table altered --查看索引 SQL> select index_name from user_indexes where index_name='PK_TEST'; INDEX_NAME ------------------------------ PK_TEST --删除主键 SQL> alter table TEST drop primary key; Table altered --再查看索引，没有被删掉 SQL> select index_name from user_indexes where index_name='PK_TEST'; INDEX_NAME ------------------------------ PK_TEST

增加主键，自动创建索引

--建表 SQL> drop table test; Table dropped SQL> create table test(ID INTEGER not null); Table created --添加主键，自动创建索引 SQL> alter table TEST   2  add CONSTRAINT PK_TEST PRIMARY KEY (ID) using index; Table altered --查看索引 SQL> select index_name from user_indexes where index_name='PK_TEST'; INDEX_NAME ------------------------------ PK_TEST --删除主键 SQL> alter table TEST drop primary key; Table altered --再次查看索引，已经删除 SQL> select index_name from user_indexes where index_name='PK_TEST'; INDEX_NAME ------------------------------

测试drop index选项时的情况

手工创建索引,后增加主键

--建表 SQL> drop table test; Table dropped SQL> create table test(ID INTEGER not null); Table created --建主键 SQL> create unique index PK_TEST on TEST(ID); Index created SQL> alter table TEST   2  add CONSTRAINT PK_TEST PRIMARY KEY (ID) using index; Table altered --查看索引 SQL> select index_name from user_indexes where index_name='PK_TEST'; INDEX_NAME ------------------------------ PK_TEST --删除主键 SQL> alter table TEST drop primary key drop index; Table altered --再次查看索引，已经被删除 SQL> select index_name from user_indexes where index_name='PK_TEST'; INDEX_NAME ------------------------------

增加主键,自动创建索引

--建表 SQL> drop table test; Table dropped SQL> create table test(ID INTEGER not null); Table created --建主键 SQL> alter table TEST   2  add CONSTRAINT PK_TEST PRIMARY KEY (ID) using index; Table altered --查看索引 SQL> select index_name from user_indexes where index_name='PK_TEST'; INDEX_NAME ------------------------------ PK_TEST --删除主键 SQL> alter table TEST drop primary key drop index; Table altered --再次查看索引，已经被删除 SQL> select index_name from user_indexes where index_name='PK_TEST'; INDEX_NAME ------------------------------

测试keep index选项时的情况

手工创建索引,后增加主键

--建表 SQL> drop table test; Table dropped SQL> create table test(ID INTEGER not null); Table created --建主键 SQL> create unique index PK_TEST on TEST(ID); Index created SQL> alter table TEST   2  add CONSTRAINT PK_TEST PRIMARY KEY (ID) using index; Table altered --查看索引 SQL> select index_name from user_indexes where index_name='PK_TEST'; INDEX_NAME ------------------------------ PK_TEST SQL> --删除主键 SQL> alter table TEST drop primary key keep index; Table altered --再次查看索引，被保留 SQL> select index_name from user_indexes where index_name='PK_TEST'; INDEX_NAME ------------------------------ PK_TEST

增加主键,自动创建索引

--建表 SQL> drop table test; Table dropped SQL> create table test(ID INTEGER not null); Table created --建主键 SQL> alter table TEST   2  add CONSTRAINT PK_TEST PRIMARY KEY (ID) using index; Table altered --查看索引 SQL> select index_name from user_indexes where index_name='PK_TEST'; INDEX_NAME ------------------------------ PK_TEST --删除主键 SQL> alter table TEST drop primary key keep index; Table altered --再次查看索引，依然被保留 SQL> select index_name from user_indexes where index_name='PK_TEST'; INDEX_NAME ------------------------------ PK_TEST

测试增加drop index选项时使用cascade关键字的作用

--新建table1 SQL> create table table1(ID INTEGER not null); Table created --新增主键约束 SQL> alter table table1 add CONSTRAINT PK_TEST PRIMARY KEY (ID) using index; Table altered --插入三个值1，2，3 SQL> insert into table1 values(1); 1 row inserted SQL> insert into table1 values(2); 1 row inserted SQL> insert into table1 values(3); 1 row inserted SQL> select * from table1;                                      ID ---------------------------------------                                       1                                       2                                       3 --新建table2 SQL> create table table2(ID1 INTEGER not null,ID2 INTEGER not null); Table created --新增table2主键约束 SQL> alter table table2 add constraint pk_table2 primary key (ID1) using index; Table altered --新增table2外键约束，其id2这一列指向table的id SQL> alter table table2 add constraint fk_id2 foreign key (ID2) references table1(id); Table altered --向table2插入三行值(1,1),(2,2),(3,3) SQL> insert into table2 values (1,1); 1 row inserted SQL> insert into table2 values (2,2); 1 row inserted SQL> insert into table2 values (3,3); 1 row inserted SQL> select * from table2;                                       ID1                                     ID2 --------------------------------------- ---------------------------------------                                       1                                       1                                       2                                       2                                       3                                       3 --查看table2的约束，可以看到有主键和外键 SQL> select * from user_constraints a where a.table_name='TABLE2';   OWNER                                                        CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME                     SEARCH_CONDITION                                                                 R_OWNER                                                      R_CONSTRAINT_NAME              DELETE_RULE STATUS   DEFERRABLE     DEFERRED  VALIDATED     GENERATED      BAD RELY LAST_CHANGE INDEX_OWNER                    INDEX_NAME                     INVALID VIEW_RELATED ------------------------------------------------------------ ------------------------------ --------------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------------------ ----------- -------- -------------- --------- ------------- -------------- --- ---- ----------- ------------------------------ ------------------------------ ------- -------------- VOUDATA                                                      SYS_C0097617                   C               TABLE2                         "ID1" IS NOT NULL                                                                                                                                                                        ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED     GENERATED NAME          2014/6/10 1                                                                        VOUDATA                                                      SYS_C0097618                   C               TABLE2                         "ID2" IS NOT NULL                                                                                                                                                                        ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED     GENERATED NAME          2014/6/10 1                                                                        VOUDATA                                                      PK_TABLE2                      P               TABLE2                                                                                                                                                                                                                  ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED     USER NAME               2014/6/10 1 VOUDATA                        PK_TABLE2                              VOUDATA                                                      FK_ID2                         R               TABLE2                                                                                                          VOUDATA                                                      PK_TEST                        NO ACTION   ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED     USER NAME               2014/6/10 1                                                                        --删除table1主键，不使用cascade关键字，此时报错 SQL> alter table table1 drop primary key drop index;   alter table table1 drop primary key drop index   ORA-02273: 此唯一/主键已被某些外键引用   --删除table1主键，使用cascade关键字，不报错 SQL> alter table table1 drop primary key cascade drop index;   Table altered   --查看table2的约束，此时外键已经被干掉了 SQL> select * from user_constraints a where a.table_name='TABLE2';   OWNER                                                        CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME                     SEARCH_CONDITION                                                                 R_OWNER                                                      R_CONSTRAINT_NAME              DELETE_RULE STATUS   DEFERRABLE     DEFERRED  VALIDATED     GENERATED      BAD RELY LAST_CHANGE INDEX_OWNER                    INDEX_NAME                     INVALID VIEW_RELATED ------------------------------------------------------------ ------------------------------ --------------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------------------ ----------- -------- -------------- --------- ------------- -------------- --- ---- ----------- ------------------------------ ------------------------------ ------- -------------- VOUDATA                                                      SYS_C0097617                   C               TABLE2                         "ID1" IS NOT NULL                                                                                                                                                                        ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED     GENERATED NAME          2014/6/10 1                                                                       VOUDATA                                                      SYS_C0097618                   C               TABLE2                         "ID2" IS NOT NULL                                                                                                                                                                        ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED     GENERATED NAME          2014/6/10 1                                                                       VOUDATA                                                      PK_TABLE2                      P               TABLE2                                                                                                                                                                                                                  ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED     USER NAME               2014/6/10 1 VOUDATA                        PK_TABLE2                              --查看table2的数据，没有变化，说明只是外键关联去掉了，引用table1的数据保留 SQL> select * from table2;                                       ID1                                     ID2 --------------------------------------- ---------------------------------------                                       1                                       1                                       2                                       2                                       3                                       3

 

由此可见，若主键被其他表引用做外键，删除主键并drop index，若不使用cascade关键字则执行会报错，而使用cascade关键字会将其关联的外键同时删除。所在这种情况下cascade关键字还是谨慎使用，在完全考虑到了外键关联不需要时再使用，否则可能在未知的情况下将某些外键关联删除并且在调整过主键后忘记重新增加外键，执行时报错总好过不知情的情况下少了外键关联。