oracle数据库删除主键约束时是否删除索引？

问题删除主键时是否会同时自动删除索引答案是否删除索引取决于索引是创建主键时自动创建的还是创建主键前手工创建的。如果期望删除主键时同时删除索引安全的做法是增加drop index选项。另外如果为了防止因存在外键引用而删除失败可以增加cascade选项。以下内容在PLSQLDeveloper中亲测为了代码便于阅读放到eclipse中做了格式调整。测试无drop index/keepindex选项时的情况手工创建索引后增加主键--建表SQLdroptabletest;droptabletestORA-00942: 表或视图不存在SQLcreatetabletest(IDINTEGERnotnull);Table created--建主键SQLcreateuniqueindexPK_TESTonTEST(ID);Index createdSQLaltertableTEST2addCONSTRAINTPK_TESTPRIMARYKEY(ID)usingindex;Table altered--查看索引SQLselectindex_namefromuser_indexeswhereindex_namePK_TEST;INDEX_NAME------------------------------PK_TEST--删除主键SQLaltertableTESTdropprimarykey;Table altered--再查看索引没有被删掉SQLselectindex_namefromuser_indexeswhereindex_namePK_TEST;INDEX_NAME------------------------------PK_TEST增加主键自动创建索引--建表SQLdroptabletest;Table droppedSQLcreatetabletest(IDINTEGERnotnull);Table created--添加主键自动创建索引SQLaltertableTEST2addCONSTRAINTPK_TESTPRIMARYKEY(ID)usingindex;Table altered--查看索引SQLselectindex_namefromuser_indexeswhereindex_namePK_TEST;INDEX_NAME------------------------------PK_TEST--删除主键SQLaltertableTESTdropprimarykey;Table altered--再次查看索引已经删除SQLselectindex_namefromuser_indexeswhereindex_namePK_TEST;INDEX_NAME------------------------------测试drop index选项时的情况手工创建索引,后增加主键--建表SQLdroptabletest;Table droppedSQLcreatetabletest(IDINTEGERnotnull);Table created--建主键SQLcreateuniqueindexPK_TESTonTEST(ID);Index createdSQLaltertableTEST2addCONSTRAINTPK_TESTPRIMARYKEY(ID)usingindex;Table altered--查看索引SQLselectindex_namefromuser_indexeswhereindex_namePK_TEST;INDEX_NAME------------------------------PK_TEST--删除主键SQLaltertableTESTdropprimarykeydropindex;Table altered--再次查看索引已经被删除SQLselectindex_namefromuser_indexeswhereindex_namePK_TEST;INDEX_NAME------------------------------增加主键,自动创建索引--建表SQLdroptabletest;Table droppedSQLcreatetabletest(IDINTEGERnotnull);Table created--建主键SQLaltertableTEST2addCONSTRAINTPK_TESTPRIMARYKEY(ID)usingindex;Table altered--查看索引SQLselectindex_namefromuser_indexeswhereindex_namePK_TEST;INDEX_NAME------------------------------PK_TEST--删除主键SQLaltertableTESTdropprimarykeydropindex;Table altered--再次查看索引已经被删除SQLselectindex_namefromuser_indexeswhereindex_namePK_TEST;INDEX_NAME------------------------------测试keep index选项时的情况手工创建索引,后增加主键--建表SQLdroptabletest;Table droppedSQLcreatetabletest(IDINTEGERnotnull);Table created--建主键SQLcreateuniqueindexPK_TESTonTEST(ID);Index createdSQLaltertableTEST2addCONSTRAINTPK_TESTPRIMARYKEY(ID)usingindex;Table altered--查看索引SQLselectindex_namefromuser_indexeswhereindex_namePK_TEST;INDEX_NAME------------------------------PK_TESTSQL--删除主键SQLaltertableTESTdropprimarykeykeepindex;Table altered--再次查看索引被保留SQLselectindex_namefromuser_indexeswhereindex_namePK_TEST;INDEX_NAME------------------------------PK_TEST增加主键,自动创建索引--建表SQLdroptabletest;Table droppedSQLcreatetabletest(IDINTEGERnotnull);Table created--建主键SQLaltertableTEST2addCONSTRAINTPK_TESTPRIMARYKEY(ID)usingindex;Table altered--查看索引SQLselectindex_namefromuser_indexeswhereindex_namePK_TEST;INDEX_NAME------------------------------PK_TEST--删除主键SQLaltertableTESTdropprimarykeykeepindex;Table altered--再次查看索引依然被保留SQLselectindex_namefromuser_indexeswhereindex_namePK_TEST;INDEX_NAME------------------------------PK_TEST测试增加drop index选项时使用cascade关键字的作用--新建table1SQLcreatetabletable1(IDINTEGERnotnull);Table created--新增主键约束SQLaltertabletable1addCONSTRAINTPK_TESTPRIMARYKEY(ID)usingindex;Table altered--插入三个值123SQLinsertintotable1values(1);1 row insertedSQLinsertintotable1values(2);1 row insertedSQLinsertintotable1values(3);1 row insertedSQLselect*fromtable1;ID---------------------------------------123--新建table2SQLcreatetabletable2(ID1INTEGERnotnull,ID2INTEGERnotnull);Table created--新增table2主键约束SQLaltertabletable2addconstraintpk_table2primarykey(ID1)usingindex;Table altered--新增table2外键约束其id2这一列指向table的idSQLaltertabletable2addconstraintfk_id2foreignkey(ID2)referencestable1(id);Table altered--向table2插入三行值(1,1),(2,2),(3,3)SQLinsertintotable2values(1,1);1 row insertedSQLinsertintotable2values(2,2);1 row insertedSQLinsertintotable2values(3,3);1 row insertedSQLselect*fromtable2;ID1 ID2--------------------------------------- ---------------------------------------1 12 23 3--查看table2的约束可以看到有主键和外键SQLselect*fromuser_constraints awherea.table_nameTABLE2;OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUSDEFERRABLEDEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED------------------------------------------------------------ ------------------------------ --------------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------------------ ----------- -------- -------------- --------- ------------- -------------- --- ---- ----------- ------------------------------ ------------------------------ ------- --------------VOUDATA SYS_C0097617 C TABLE2ID1ISNOTNULLENABLEDNOTDEFERRABLEIMMEDIATEVALIDATED GENERATED NAME 2014/6/10 1VOUDATA SYS_C0097618 C TABLE2ID2ISNOTNULLENABLEDNOTDEFERRABLEIMMEDIATEVALIDATED GENERATED NAME 2014/6/10 1VOUDATA PK_TABLE2 P TABLE2 ENABLEDNOTDEFERRABLEIMMEDIATEVALIDATEDUSERNAME 2014/6/10 1 VOUDATA PK_TABLE2VOUDATAFK_ID2R TABLE2 VOUDATA PK_TESTNOACTIONENABLEDNOTDEFERRABLEIMMEDIATEVALIDATEDUSERNAME 2014/6/10 1--删除table1主键不使用cascade关键字此时报错SQLaltertabletable1dropprimarykeydropindex;altertabletable1dropprimarykeydropindexORA-02273: 此唯一/主键已被某些外键引用--删除table1主键使用cascade关键字不报错SQLaltertabletable1dropprimarykeycascadedropindex;Table altered--查看table2的约束此时外键已经被干掉了SQLselect*fromuser_constraints awherea.table_nameTABLE2;OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUSDEFERRABLEDEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED------------------------------------------------------------ ------------------------------ --------------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------------------ ----------- -------- -------------- --------- ------------- -------------- --- ---- ----------- ------------------------------ ------------------------------ ------- --------------VOUDATA SYS_C0097617 C TABLE2ID1ISNOTNULLENABLEDNOTDEFERRABLEIMMEDIATEVALIDATED GENERATED NAME 2014/6/10 1VOUDATA SYS_C0097618 C TABLE2ID2ISNOTNULLENABLEDNOTDEFERRABLEIMMEDIATEVALIDATED GENERATED NAME 2014/6/10 1VOUDATA PK_TABLE2 P TABLE2 ENABLEDNOTDEFERRABLEIMMEDIATEVALIDATEDUSERNAME 2014/6/10 1 VOUDATA PK_TABLE2--查看table2的数据没有变化说明只是外键关联去掉了引用table1的数据保留SQLselect*fromtable2;ID1 ID2--------------------------------------- ---------------------------------------1 12 23 3由此可见若主键被其他表引用做外键删除主键并drop index若不使用cascade关键字则执行会报错而使用cascade关键字会将其关联的外键同时删除。所在这种情况下cascade关键字还是谨慎使用在完全考虑到了外键关联不需要时再使用否则可能在未知的情况下将某些外键关联删除并且在调整过主键后忘记重新增加外键执行时报错总好过不知情的情况下少了外键关联。