Tuesday, October 9, 2007

ORA -2429:Cannot Drop Index used for enforcement of Unique/primary key

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

5 comments: