Day: January 5, 2021

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

While dropping an index,all the constraints cascaded to that index should be either disabled or dropped

SQL> !oerr ora 02429
02429, 00000, "cannot drop index used for enforcement of unique/primary key"
// *Cause: user attempted to drop an index that is being used as the
//         enforcement mechanism for unique or primary key.
// *Action: drop the constraint instead of the index.

Solution:

Check the constraints related to that index

SQL> select constraint_name from dba_constraints where table_name in ('T','S');

CONSTRAINT_NAME
--------------------------------------------------------------------------------
PK_T
FK_T
PK_S

Primary key and foreign key for the table is disabled and respective index is wiped!

 SQL> alter table t disable constraint FK_T;

Table altered.

SQL> alter table s disable constraint PK_S;

Table altered.