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

I got ” ORA-02429: cannot drop index used for enforcement of unique/primary key ” error in Oracle database during drop index.

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

Details of error are as follows.

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


SQL>

cannot drop index used for enforcement of unique/primary key

This ORA-02429 error is related with the constraint related with the index.

To solve this error, drop unique or primary key constraint , then drop the related index.

SQL> col owner for a10
SQL> col constraint_name for a10
SQL> col constraint_type for a10
SQL> Select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE from DBA_CONSTRAINTS where table_name='TEST_TABLE';

OWNER CONSTRAINT CONSTRAINT
---------- ---------- ----------
MSDBA CONS_TEST_TABLE P


1 rows selected.

SQL>

Now firstly drop the Constraint of this index, then drop the index as follows.

SQL> ALTER TABLE MSDBA.TEST_TABLE DROP CONSTRAINT CONST_TEST_TABLE;

Table altered.

SQL> DROP INDEX MSDBA.IX_TEST_TABLE;

Index dropped.

SQL>

Do you want to learn more details about RMAN, then Click this Link and read the articles.

RMAN Tutorial | Backup, Restore and Recovery Tutorials For Beginner Oracle DBA