ORA-02449 Unique/Primary Keys Referenced by Foreign Keys

ORA-02449: Unique/Primary Keys in Table Referenced by Foreign Keys

Error Overview

Error Text: ORA-02449: unique/primary keys in table referenced by foreign keys

The ORA-02449 error occurs when attempting to drop a table or constraint that is referenced by foreign key constraints in other tables. Oracle prevents this to maintain referential integrity across the database.

Common Causes

1. Dropping Parent Table

• Attempting to drop a table with child references
• Cleanup scripts not handling dependencies
• Deployment scripts in wrong order

2. Dropping Primary Key Constraint

• Removing PK that is referenced by FKs
• Altering table structure
• Constraint reorganization

3. Truncating Referenced Table

• Using TRUNCATE on parent table
• Attempting to empty referenced table

4. Modifying Referenced Column

• Changing data type of referenced column
• Reducing column size
• Altering column definition

Diagnostic Queries

Find Referencing Foreign Keys

-- Find all FKs referencing a table
SELECT
  c.owner as fk_owner,
  c.table_name as fk_table,
  c.constraint_name as fk_name,
  cc.column_name as fk_column,
  r.table_name as ref_table,
  rc.column_name as ref_column
FROM dba_constraints c
JOIN dba_constraints r
  ON c.r_constraint_name = r.constraint_name
  AND c.r_owner = r.owner
JOIN dba_cons_columns cc
  ON c.constraint_name = cc.constraint_name
  AND c.owner = cc.owner
JOIN dba_cons_columns rc
  ON r.constraint_name = rc.constraint_name
  AND r.owner = rc.owner
WHERE r.table_name = UPPER('&table_name')
  AND c.constraint_type = 'R'
ORDER BY c.table_name;

-- Simpler query for current schema
SELECT
  table_name as child_table,
  constraint_name as fk_constraint,
  r_constraint_name as parent_constraint
FROM user_constraints
WHERE constraint_type = 'R'
  AND r_constraint_name IN (
    SELECT constraint_name
    FROM user_constraints
    WHERE table_name = UPPER('&table_name')
  );

Find All Dependencies

-- Complete dependency tree
WITH constraint_tree AS (
  -- Base: the table we want to drop
  SELECT
    table_name,
    constraint_name,
    constraint_type,
    r_constraint_name,
    0 as level
  FROM user_constraints
  WHERE table_name = UPPER('&table_name')
    AND constraint_type IN ('P', 'U')

  UNION ALL

  -- Recursive: find all referencing constraints
  SELECT
    c.table_name,
    c.constraint_name,
    c.constraint_type,
    c.r_constraint_name,
    ct.level + 1
  FROM user_constraints c
  JOIN constraint_tree ct
    ON c.r_constraint_name = ct.constraint_name
  WHERE c.constraint_type = 'R'
)
SELECT
  LPAD(' ', level * 2) || table_name as hierarchy,
  constraint_name,
  constraint_type,
  r_constraint_name as references
FROM constraint_tree
ORDER BY level, table_name;

Count Child Records

-- Check if child tables have data
SELECT
  c.table_name as child_table,
  c.constraint_name as fk_name,
  (SELECT COUNT(*) FROM user_tables WHERE table_name = c.table_name) as child_rows
FROM user_constraints c
WHERE c.constraint_type = 'R'
  AND c.r_constraint_name IN (
    SELECT constraint_name
    FROM user_constraints
    WHERE table_name = UPPER('&table_name')
      AND constraint_type IN ('P', 'U')
  );

Resolution Steps

1. Drop with CASCADE CONSTRAINTS

-- Drop table and all referencing FKs
DROP TABLE parent_table CASCADE CONSTRAINTS;

-- This will:
-- 1. Drop all foreign keys referencing this table
-- 2. Drop the table itself
-- Note: Child tables are NOT dropped, only their FK constraints

2. Drop Foreign Keys First

-- Generate drop statements for FKs
SELECT
  'ALTER TABLE ' || table_name || ' DROP CONSTRAINT ' || constraint_name || ';'
  as drop_statement
FROM user_constraints
WHERE constraint_type = 'R'
  AND r_constraint_name IN (
    SELECT constraint_name
    FROM user_constraints
    WHERE table_name = UPPER('&table_name')
  );

-- Execute the drops, then drop the table
ALTER TABLE child_table1 DROP CONSTRAINT fk_child1;
ALTER TABLE child_table2 DROP CONSTRAINT fk_child2;
DROP TABLE parent_table;

3. Disable Foreign Keys Temporarily

-- Disable all referencing FKs
BEGIN
  FOR rec IN (
    SELECT table_name, constraint_name
    FROM user_constraints
    WHERE constraint_type = 'R'
      AND r_constraint_name IN (
        SELECT constraint_name
        FROM user_constraints
        WHERE table_name = UPPER('&parent_table')
      )
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER TABLE ' || rec.table_name ||
      ' DISABLE CONSTRAINT ' || rec.constraint_name;
  END LOOP;
END;
/

-- Perform your operation
DROP TABLE parent_table;
-- or TRUNCATE, etc.

-- Note: Disabling leaves constraints in place
-- You'll need to drop or fix them if parent table is dropped

4. Delete Child Data First

-- Delete in correct order (leaves to root)
-- First, identify the order
WITH delete_order AS (
  SELECT
    table_name,
    constraint_name,
    r_constraint_name,
    LEVEL as del_order
  FROM user_constraints
  WHERE constraint_type = 'R'
  START WITH r_constraint_name IN (
    SELECT constraint_name
    FROM user_constraints
    WHERE table_name = UPPER('&parent_table')
  )
  CONNECT BY PRIOR constraint_name = r_constraint_name
)
SELECT * FROM delete_order ORDER BY del_order DESC;

-- Delete from children first
DELETE FROM grandchild_table WHERE parent_id IN (SELECT id FROM child_table);
DELETE FROM child_table WHERE parent_id IN (SELECT id FROM parent_table);
DELETE FROM parent_table;

5. Handle TRUNCATE Requirement

-- Can't TRUNCATE with FK references
-- Option 1: Disable FKs, truncate, re-enable
BEGIN
  -- Disable FKs
  FOR rec IN (
    SELECT table_name, constraint_name
    FROM user_constraints
    WHERE constraint_type = 'R'
      AND r_constraint_name IN (
        SELECT constraint_name FROM user_constraints
        WHERE table_name = 'PARENT_TABLE'
      )
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name ||
      ' DISABLE CONSTRAINT ' || rec.constraint_name;
  END LOOP;

  -- Truncate
  EXECUTE IMMEDIATE 'TRUNCATE TABLE parent_table';

  -- Re-enable (will fail if child data exists!)
  FOR rec IN (
    SELECT table_name, constraint_name
    FROM user_constraints
    WHERE constraint_type = 'R'
      AND r_constraint_name IN (
        SELECT constraint_name FROM user_constraints
        WHERE table_name = 'PARENT_TABLE'
      )
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name ||
      ' ENABLE CONSTRAINT ' || rec.constraint_name;
  END LOOP;
END;
/

-- Option 2: Truncate children first (if cascade delete desired)
TRUNCATE TABLE grandchild_table;
TRUNCATE TABLE child_table;
TRUNCATE TABLE parent_table;

Prevention Strategies

1. Use ON DELETE CASCADE

-- Create FK with cascade delete
ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id) REFERENCES parent_table(id)
ON DELETE CASCADE;

-- Now deleting from parent automatically deletes children
DELETE FROM parent_table WHERE id = 100;
-- Child records deleted automatically

2. Create Drop Procedure

-- Safe table drop procedure
CREATE OR REPLACE PROCEDURE safe_drop_table(p_table_name VARCHAR2) AS
  v_count NUMBER;
BEGIN
  -- Check for dependencies
  SELECT COUNT(*) INTO v_count
  FROM user_constraints
  WHERE constraint_type = 'R'
    AND r_constraint_name IN (
      SELECT constraint_name
      FROM user_constraints
      WHERE table_name = UPPER(p_table_name)
    );

  IF v_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Table has ' || v_count || ' referencing FK constraints.');
    DBMS_OUTPUT.PUT_LINE('Using CASCADE CONSTRAINTS...');
  END IF;

  EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name || ' CASCADE CONSTRAINTS PURGE';
  DBMS_OUTPUT.PUT_LINE('Table ' || p_table_name || ' dropped successfully.');

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    RAISE;
END;
/

3. Document Dependencies

-- Generate dependency documentation
SELECT
  'Table: ' || p.table_name || CHR(10) ||
  '  Referenced by: ' || c.table_name || '.' || c.constraint_name
  as dependency_doc
FROM user_constraints p
JOIN user_constraints c
  ON p.constraint_name = c.r_constraint_name
WHERE p.constraint_type IN ('P', 'U')
  AND c.constraint_type = 'R'
ORDER BY p.table_name, c.table_name;

4. Ordered Drop Scripts

-- Generate ordered drop script
WITH table_order AS (
  SELECT
    table_name,
    LEVEL as drop_order
  FROM (
    SELECT DISTINCT table_name
    FROM user_constraints
    WHERE constraint_type = 'R'
  )
  START WITH table_name NOT IN (
    SELECT DISTINCT r_constraint_name
    FROM user_constraints
    WHERE constraint_type = 'R'
  )
  CONNECT BY PRIOR table_name IN (
    SELECT table_name FROM user_constraints
    WHERE r_constraint_name = PRIOR constraint_name
  )
)
SELECT 'DROP TABLE ' || table_name || ' CASCADE CONSTRAINTS;' as drop_stmt
FROM table_order
ORDER BY drop_order DESC;

Understanding Referential Integrity

Scenario	Result
DROP TABLE parent	ORA-02449 if FKs exist
DROP TABLE parent CASCADE CONSTRAINTS	FKs dropped, table dropped
TRUNCATE TABLE parent	ORA-02266 if FKs exist
DELETE FROM parent (with ON DELETE CASCADE)	Child rows deleted
DELETE FROM parent (without cascade)	ORA-02292 if child rows exist

Related Errors

• ORA-02266 - Unique/primary keys referenced (for TRUNCATE)
• ORA-02292 - Integrity constraint violated - child record found
• ORA-02291 - Integrity constraint violated - parent key not found
• ORA-02297 - Cannot disable constraint - dependencies exist

Emergency Response

Quick Drop with CASCADE

-- Fastest way to drop a referenced table
DROP TABLE problem_table CASCADE CONSTRAINTS PURGE;

Find and Drop All FKs to a Table

-- One block to remove all references
BEGIN
  FOR rec IN (
    SELECT table_name, constraint_name
    FROM user_constraints
    WHERE constraint_type = 'R'
      AND r_constraint_name IN (
        SELECT constraint_name FROM user_constraints
        WHERE table_name = UPPER('&parent_table')
      )
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name ||
      ' DROP CONSTRAINT ' || rec.constraint_name;
    DBMS_OUTPUT.PUT_LINE('Dropped: ' || rec.table_name || '.' || rec.constraint_name);
  END LOOP;
END;
/