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");