ORA-02429 Cannot Drop Index Used for Enforcement of Unique/Primary Key

ORA-02429: Cannot Drop Index Used for Enforcement of Unique/Primary Key

Error Overview

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

The ORA-02429 error occurs when attempting to drop an index that is being used to enforce a PRIMARY KEY or UNIQUE constraint. Oracle creates and uses indexes to efficiently enforce uniqueness, and the index cannot be dropped while the constraint depends on it.

Common Causes

1. Direct Index Drop Attempt

• Trying to DROP INDEX on constraint-backing index
• Not realizing index is tied to constraint
• Scripts attempting to rebuild by drop/create

2. Constraint-Index Confusion

• Index created automatically with constraint
• Index created before constraint and adopted
• Unclear naming conventions

3. Cleanup or Maintenance

• Attempting to reclaim space
• Index rebuild strategies
• Storage reorganization

4. Migration/Upgrade Activities

• Moving to different tablespace
• Changing index type
• Partition strategy changes

Diagnostic Queries

Identify Constraint Using Index

-- Find constraint associated with index
SELECT
  c.constraint_name,
  c.constraint_type,
  c.table_name,
  c.index_name
FROM user_constraints c
WHERE c.index_name = UPPER('&index_name');

-- Find all constraints and their indexes
SELECT
  table_name,
  constraint_name,
  constraint_type,
  index_name,
  status
FROM user_constraints
WHERE index_name IS NOT NULL
ORDER BY table_name, constraint_name;

Check Index-Constraint Relationship

-- Detailed view of index and constraint
SELECT
  i.index_name,
  i.table_name,
  i.uniqueness,
  i.status as index_status,
  c.constraint_name,
  c.constraint_type,
  c.status as constraint_status
FROM user_indexes i
LEFT JOIN user_constraints c
  ON i.index_name = c.index_name
WHERE i.index_name = UPPER('&index_name');

-- Check if index was created with constraint or separately
SELECT
  i.index_name,
  i.table_name,
  c.constraint_name,
  CASE
    WHEN i.index_name = c.constraint_name THEN 'CREATED WITH CONSTRAINT'
    ELSE 'CREATED SEPARATELY'
  END as origin
FROM user_indexes i
JOIN user_constraints c ON i.index_name = c.index_name
WHERE c.constraint_type IN ('P', 'U');

View Index Columns

-- See what columns the index covers
SELECT
  index_name,
  column_name,
  column_position
FROM user_ind_columns
WHERE index_name = UPPER('&index_name')
ORDER BY column_position;

-- Compare with constraint columns
SELECT
  c.constraint_name,
  cc.column_name,
  cc.position
FROM user_constraints c
JOIN user_cons_columns cc
  ON c.constraint_name = cc.constraint_name
WHERE c.index_name = UPPER('&index_name')
ORDER BY cc.position;

Resolution Steps

1. Drop the Constraint First

-- If you want to remove both constraint and index
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
-- Index is automatically dropped if it was created with the constraint

-- Verify index is gone
SELECT index_name FROM user_indexes
WHERE index_name = UPPER('&index_name');

2. Drop Constraint with KEEP INDEX

-- Drop constraint but keep the index (as non-unique)
ALTER TABLE table_name DROP CONSTRAINT constraint_name KEEP INDEX;

-- Now you can drop the index if needed
DROP INDEX index_name;

-- Or rebuild it as needed
ALTER INDEX index_name REBUILD TABLESPACE new_tablespace;

3. Disable Constraint, Manage Index, Re-enable

-- Disable the constraint
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

-- Now you can drop and recreate the index
DROP INDEX old_index_name;

CREATE UNIQUE INDEX new_index_name
ON table_name (column1, column2)
TABLESPACE new_tablespace;

-- Re-enable constraint using new index
ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name
USING INDEX new_index_name;

4. Rebuild Index Without Dropping

-- Rebuild in place
ALTER INDEX index_name REBUILD;

-- Rebuild in different tablespace
ALTER INDEX index_name REBUILD TABLESPACE new_tablespace;

-- Rebuild online (no blocking)
ALTER INDEX index_name REBUILD ONLINE;

-- Rebuild with new storage parameters
ALTER INDEX index_name REBUILD
TABLESPACE new_tablespace
STORAGE (INITIAL 100M NEXT 50M);

5. Move Index to Different Tablespace

-- Move constraint index to new tablespace
-- Option A: Rebuild online
ALTER INDEX constraint_index REBUILD TABLESPACE new_ts ONLINE;

-- Option B: Drop and recreate constraint with new storage
ALTER TABLE table_name DROP CONSTRAINT pk_table;

CREATE UNIQUE INDEX pk_table_idx
ON table_name (id)
TABLESPACE new_ts;

ALTER TABLE table_name
ADD CONSTRAINT pk_table PRIMARY KEY (id)
USING INDEX pk_table_idx;

6. Replace with Different Index Type

-- Example: Replace B-tree with bitmap (for data warehouse)
-- Note: Bitmap indexes cannot enforce constraints

-- For replacing with same type but different structure:
ALTER TABLE table_name DROP CONSTRAINT pk_table;

-- Create new index with desired properties
CREATE UNIQUE INDEX pk_table_new
ON table_name (id)
TABLESPACE idx_ts
COMPRESS 1;  -- Or other options

-- Recreate constraint
ALTER TABLE table_name
ADD CONSTRAINT pk_table PRIMARY KEY (id)
USING INDEX pk_table_new;

Prevention Strategies

1. Name Indexes and Constraints Clearly

-- Use clear naming conventions
CREATE TABLE employees (
  id NUMBER,
  email VARCHAR2(100)
);

-- Create index first with clear name
CREATE UNIQUE INDEX idx_emp_id ON employees(id) TABLESPACE idx_ts;
CREATE UNIQUE INDEX idx_emp_email ON employees(email) TABLESPACE idx_ts;

-- Create constraints using those indexes
ALTER TABLE employees ADD CONSTRAINT pk_employees PRIMARY KEY (id)
USING INDEX idx_emp_id;

ALTER TABLE employees ADD CONSTRAINT uk_emp_email UNIQUE (email)
USING INDEX idx_emp_email;

2. Document Index-Constraint Relationships

-- Generate documentation
SELECT
  'Table: ' || i.table_name || CHR(10) ||
  '  Index: ' || i.index_name || ' (' || i.uniqueness || ')' || CHR(10) ||
  '  Enforces: ' || NVL(c.constraint_name, 'No constraint') ||
  CASE WHEN c.constraint_type IS NOT NULL
       THEN ' (' || c.constraint_type || ')'
       ELSE ''
  END as documentation
FROM user_indexes i
LEFT JOIN user_constraints c ON i.index_name = c.index_name
WHERE i.uniqueness = 'UNIQUE'
ORDER BY i.table_name;

3. Create Maintenance Procedures

-- Safe index rebuild procedure
CREATE OR REPLACE PROCEDURE safe_rebuild_index(
  p_index_name VARCHAR2,
  p_tablespace VARCHAR2 DEFAULT NULL
) AS
  v_constraint_name VARCHAR2(128);
  v_table_name VARCHAR2(128);
  v_rebuild_sql VARCHAR2(500);
BEGIN
  -- Check if index enforces a constraint
  BEGIN
    SELECT constraint_name, table_name
    INTO v_constraint_name, v_table_name
    FROM user_constraints
    WHERE index_name = UPPER(p_index_name);

    DBMS_OUTPUT.PUT_LINE('Index enforces constraint: ' || v_constraint_name);
    DBMS_OUTPUT.PUT_LINE('Using REBUILD instead of DROP/CREATE');
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Index does not enforce any constraint');
  END;

  -- Build rebuild statement
  v_rebuild_sql := 'ALTER INDEX ' || p_index_name || ' REBUILD';
  IF p_tablespace IS NOT NULL THEN
    v_rebuild_sql := v_rebuild_sql || ' TABLESPACE ' || p_tablespace;
  END IF;
  v_rebuild_sql := v_rebuild_sql || ' ONLINE';

  -- Execute
  DBMS_OUTPUT.PUT_LINE('Executing: ' || v_rebuild_sql);
  EXECUTE IMMEDIATE v_rebuild_sql;
  DBMS_OUTPUT.PUT_LINE('Rebuild complete');
END;
/

4. Use DBMS_METADATA for Scripting

-- Get complete DDL for constraint and index
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name) as ddl
FROM user_constraints
WHERE index_name = UPPER('&index_name');

SELECT DBMS_METADATA.GET_DDL('INDEX', '&index_name') as ddl
FROM dual;

Understanding Index-Constraint Relationship

Constraint Created	Index Behavior
Without USING INDEX	Auto-creates index with same name
USING INDEX idx_name	Uses existing index
DROP CONSTRAINT	Drops auto-created index; keeps USING INDEX
DROP CONSTRAINT KEEP INDEX	Keeps index (becomes non-unique for PK/UK)

Related Errors

• ORA-02449 - Unique/primary keys referenced by foreign keys
• ORA-02299 - Cannot validate constraint
• ORA-01408 - Such column list already indexed
• ORA-00955 - Name already used

Emergency Response

Quick Check Before DROP INDEX

-- Check if any constraint uses this index
SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE index_name = UPPER('&index_name');

-- If results returned, you must drop constraint first or use REBUILD

Rebuild Instead of Drop

-- Almost always, REBUILD achieves the goal
ALTER INDEX problem_index REBUILD ONLINE TABLESPACE new_ts;