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.

Drop Constraint

The constraint name may or may not be the same as the index name, we should find it out.

SQL> column constraint_name format a30;
SQL> select constraint_name from all_constraints where index_owner = 'HR' and index_name = 'JHIST_EMP_ID_ST_DATE_PK';

CONSTRAINT_NAME
------------------------------
JHIST_EMP_ID_ST_DATE_PK

Then we drop the constraint.

SQL> alter table hr.job_history drop constraint JHIST_EMP_ID_ST_DATE_PK;

Table altered.

Drop Index

Then drop the index if any. I've talked more 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 *