To simulate the error do the following:
create table temp_index as select object_name from user_objects where object_type='INDEX';
create table testing(testid number primary key, testchar varchar2(200));
Find out the index created for the primary key by the following statement:
select object_name from user_objects where object_type='INDEX'
minus
select object_name from temp_index;
Drop the index by issuing the following statement:
drop index SYS_C00249876;
You will get the error ORA-02429: Cannot drop index used for enforcement of unique/primary key
In Index organized tables this problem will be more. You will not be able to drop either the primary key or index.
Consider the example:
CREATE TABLE temp1 (
testid NUMBER (3) NOT NULL,
CONSTRAINT pk_temp1
PRIMARY KEY ( testid))
ORGANIZATION INDEX ;
alter table temp1 drop constraint pk_temp1;
You will get the following error:
ORA-25188: Cannot drop/disable/defer the primary key constraint for index-organized tables or sorted hash cluster
drop index pk_temp1;
You will get the following error:
ORA-02429: Cannot drop index used for enforcement of unique/primary key
