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

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

I tried to drop the index from my table and i am getting the error.

Error:

SQL> drop index tbl_pk;
drop index tbl_pk
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

Solution:
To delete the index from the table you have to delete its constraint. As per error index is created with unique/primary key.

Check the name of constraint and constraint type:

col owner for a10
col constraint_name for a10
col constraint_type for a10
Select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE from DBA_CONSTRAINTS where table_name='K';
OWNER CONSTRAINT CONSTRAINT
---------- ---------- ----------
SYS TBL_PK P

Constraint Type:
C (check constraint a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (read-only on view)

Drop the constraint from the table to delete index

ALTER TABLE K DROP CONSTRAINT TBL_PK;
Table altered.

Advertisements

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 )

Google photo

You are commenting using your Google 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.