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

 2,618 views last month,  6 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply