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

Drop or create constraints to change the Primary or unique key index in Oracle

I tried to drop the index from my table but I am getting the following error. Ora-02429: Cannot drop index used for enforcement of unique/Primary Key.

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 the error-index is created with a unique/primary key.

Check the name of the constraint and constraint type:

Note: Primary and unique key has indexed with them so we are checking name for only those constraints

 col owner for a15
 col constraint_name for a25
 col constraint_type for a25
 Select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE from DBA_CONSTRAINTS where  table_name='Employee' and constraint_name in ('P','U');
 OWNER         CONSTRAINT_NAME     CONSTRAINT_TYPE
 ----------    --------------      ---------------
 HR            Employee_ID_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)

Get the DDL for the index or Constraint which you are going to Drop:


set echo off;
Set pages 999;
set long 90000;
select  dbms_metadata.get_ddl('CONSTRAINT','constraint_name','schema_name') as output from  dual;

Drop the constraint from the table to delete the index:

ALTER TABLE schema_name.table_name DROP CONSTRAINT contraint_name; 
Example:
ALTER TABLE EMPLOYEE DROP CONSTRAINT EMPLOYEE_ID_PK;
Table altered.

Create the Constraint again for the table:

ALTER TABLE schema_name.table_name ADD CONSTRAINT constraint_name PRIMARY KEY ("column1", "column2", "column3");
Advertisement

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 )

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.