How to Resolve ORA-02429: cannot drop index used for enforcement of unique/primary key

  • by

ORA-02429

ORA-02429 means that the index you want to drop is a primary key.

SQL> drop index hr.JHIST_EMP_ID_ST_DATE_PK;
drop index hr.JHIST_EMP_ID_ST_DATE_PK
              *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

Although primary key is an index, it's also a constraint. You should treat it like a constraint first, then drop it as an index.

SQL> alter table hr.job_history drop constraint JHIST_EMP_ID_ST_DATE_PK;

Table altered.

Then drop the index if any. I talked about this in How to Drop Primary Key.

SQL> drop index hr.JHIST_EMP_ID_ST_DATE_PK;

Index dropped.

The index has been dropped.

Leave a Reply

Your email address will not be published. Required fields are marked *