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.