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

I believe many people have encountered the ORA-02429: cannot drop index used for enforcement of unique/primary key error, the corresponding Chinese prompt "ORA-02429: Unable to delete the index used to force a unique/primary key ", in fact, the error message is already obvious. The following uses a simple example to describe the ins and outs of the error.

The ORA-02429 error occurs because the user tries to delete an index that forces a unique/primary key, and the solution is simple, removing the corresponding constraint will automatically delete the index.

[Oracle @ DB-Server ~] $ Oerr ora 1, 2429

02429,000 00, "cannot drop index used for enforcement of unique/primary key"

// * Cause: user attempted to drop an index that is being used as

// Enforcement mechanic for unique or primary key.

// * Action: drop the constraint instead of the index.

1. Create a test table TAB_TEST as follows:

CREATE TABLE TAB_TEST

(

  JOB_ORDER_NO    VARCHAR2(20 BYTE),

  DIMM_ID         NUMBER,

  MRP_GROUP_CD    VARCHAR2(10 BYTE),

  ITEM_CAT        VARCHAR2(20 BYTE),

  REQUIRED_DATE   DATE,

  PURCHASED_BY    VARCHAR2(10 BYTE),

  USED_BY         VARCHAR2(10 BYTE),

  SUPPLIER_CD     VARCHAR2(10 BYTE)

)

2: Add a primary key constraint, as shown below:

ALTER TABLE TAB_TEST

 ADD CONSTRAINT PK_TAB_TEST

  PRIMARY KEY

  (JOB_ORDER_NO, DIMM_ID, MRP_GROUP_CD, ITEM_CAT);

3: view the index information of the test table

SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='TAB_TEST';

INDEX_NAME

------------------------------

PK_TAB_TEST

4: view the constraints of the test table:

SQL> SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='TAB_TEST';

CONSTRAINT_NAME

------------------------------

PK_TAB_TEST

5: Delete the index PK_TAB_TEST of the test table.

SQL> DROP INDEX PK_TAB_TEST;

DROP INDEX PK_TAB_TEST

           *

ERROR at line 1:

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

Solution:

Deleting the corresponding constraint will automatically delete the index. Instead of deleting the index directly. Many cainiao will do this directly!

SQL> ALTER TABLE TAB_TEST DROP CONSTRAINT PK_TAB_TEST;

Table altered.

SQL> SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='CONSTRAINT_NAME';

no rows selected

SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='TAB_TEST';

no rows selected

SQL>