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.


Check the constraints related to that index

SQL> select constraint_name from dba_constraints where table_name in ('T','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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s