ORA-02266: Keys Referenced by Foreign Keys - Fix Truncate

ORA-02266: Unique/Primary Keys Referenced by Foreign Keys

Error Overview

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

The ORA-02266 error occurs when you attempt to TRUNCATE a table whose primary or unique key is referenced by an enabled foreign key constraint in another table. Oracle prevents the truncation because it cannot verify referential integrity without scanning every child table row by row — and TRUNCATE is a DDL operation that bypasses row-level checks. This error is distinct from ORA-02449, which appears when attempting to DROP a column or constraint, and from ORA-02291/ORA-02292 which are DML-time foreign key violations.

Common Causes

1. TRUNCATE on a Parent Table with Child References

• Attempting to truncate a lookup or reference table that has dependent child rows
• Data warehouse dimension table referenced by fact tables cannot be truncated directly
• ETL refresh process trying to reload a parent table without first handling the children

2. Test Data Reset Scripts

• Development database cleanup scripts truncating tables in the wrong order
• Test teardown procedures not accounting for foreign key dependency order
• TRUNCATE TABLE issued without checking referencing constraints first

3. Cascading Truncate Not Used

• Oracle 12c+ supports TRUNCATE TABLE ... CASCADE but it must be explicitly specified
• Older scripts ported from other databases that support automatic cascade truncation
• DBA unaware of the CASCADE option

4. Constraints Re-enabled After Data Load

• Foreign keys were disabled for a bulk load but re-enabled before a follow-up truncate
• Mixed state where some child foreign keys are enabled and others are disabled

Diagnostic Queries

Identify All Foreign Keys Referencing a Table

-- Find all foreign keys that reference a specific parent table
SELECT
  c.owner               AS fk_owner,
  c.table_name          AS child_table,
  c.constraint_name     AS fk_constraint,
  c.status              AS fk_status,
  r.owner               AS parent_owner,
  r.table_name          AS parent_table,
  r.constraint_name     AS parent_constraint
FROM dba_constraints c
JOIN dba_constraints r
  ON c.r_constraint_name = r.constraint_name
  AND c.r_owner          = r.owner
WHERE r.table_name = UPPER('&parent_table_name')
  AND r.owner      = UPPER('&parent_owner')
  AND c.constraint_type = 'R'
ORDER BY c.owner, c.table_name;

-- Check for child rows that would block truncate
SELECT
  c.table_name AS child_table,
  c.constraint_name AS fk_name
FROM dba_constraints c
JOIN dba_constraints p
  ON c.r_constraint_name = p.constraint_name
WHERE p.table_name = UPPER('&parent_table_name')
  AND p.owner      = USER
  AND c.status     = 'ENABLED';

Check Constraint Status Across a Schema

-- List all foreign key constraints and their status
SELECT
  owner,
  table_name,
  constraint_name,
  status,
  validated,
  rely,
  last_change
FROM dba_constraints
WHERE constraint_type = 'R'
  AND owner = USER
ORDER BY table_name, constraint_name;

-- Find tables with both enabled and disabled foreign keys (mixed state)
SELECT table_name, status, COUNT(*) AS fk_count
FROM dba_constraints
WHERE constraint_type = 'R'
  AND owner = USER
GROUP BY table_name, status
ORDER BY table_name;

Map Full Parent-Child Dependency Tree

-- Hierarchical query to map FK dependency chain
SELECT
  LPAD(' ', 2 * (LEVEL - 1)) || table_name AS dependency_tree,
  constraint_name,
  status,
  LEVEL AS depth
FROM dba_constraints
START WITH table_name = UPPER('&root_table')
       AND constraint_type IN ('P', 'U')
       AND owner = USER
CONNECT BY PRIOR constraint_name = r_constraint_name
       AND PRIOR owner = r_owner
ORDER SIBLINGS BY table_name;

Step-by-Step Resolution

1. Use TRUNCATE TABLE … CASCADE (Oracle 12c+)

The simplest fix on Oracle 12c and later is to use the CASCADE option, which automatically truncates all child tables:

-- Truncate parent and all referencing child tables in one statement
TRUNCATE TABLE parent_table CASCADE;

-- Verify all affected tables are now empty
SELECT table_name, num_rows
FROM user_tables
WHERE table_name IN ('PARENT_TABLE', 'CHILD_TABLE_A', 'CHILD_TABLE_B')
ORDER BY table_name;

Warning: CASCADE truncates all child tables. Verify this is the intended behavior before using it in production.

2. Disable Foreign Keys, Truncate, Re-enable

When you want to truncate only the parent table and reload data while preserving child structure:

-- Step 1: Disable all foreign keys referencing the parent
BEGIN
  FOR fk IN (
    SELECT c.owner, c.table_name, c.constraint_name
    FROM dba_constraints c
    JOIN dba_constraints p
      ON c.r_constraint_name = p.constraint_name
      AND c.r_owner = p.owner
    WHERE p.table_name = 'PARENT_TABLE'
      AND p.owner = USER
      AND c.constraint_type = 'R'
      AND c.status = 'ENABLED'
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER TABLE ' || fk.owner || '.' || fk.table_name ||
      ' DISABLE CONSTRAINT ' || fk.constraint_name;
    DBMS_OUTPUT.PUT_LINE('Disabled: ' || fk.constraint_name);
  END LOOP;
END;
/

-- Step 2: Truncate the parent table
TRUNCATE TABLE parent_table;

-- Step 3: Reload parent data
INSERT INTO parent_table SELECT * FROM parent_table_staging;
COMMIT;

-- Step 4: Re-enable all foreign keys
BEGIN
  FOR fk IN (
    SELECT c.owner, c.table_name, c.constraint_name
    FROM dba_constraints c
    JOIN dba_constraints p
      ON c.r_constraint_name = p.constraint_name
      AND c.r_owner = p.owner
    WHERE p.table_name = 'PARENT_TABLE'
      AND p.owner = USER
      AND c.constraint_type = 'R'
      AND c.status = 'DISABLED'
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER TABLE ' || fk.owner || '.' || fk.table_name ||
      ' ENABLE CONSTRAINT ' || fk.constraint_name;
    DBMS_OUTPUT.PUT_LINE('Enabled: ' || fk.constraint_name);
  END LOOP;
END;
/

3. Use DELETE Instead of TRUNCATE

When referential integrity must be maintained (no CASCADE, no disabling), use DELETE:

-- DELETE respects foreign key constraints and rolls back if violated
-- Must delete child rows first, then parent

-- Step 1: Delete child rows
DELETE FROM child_table WHERE parent_id IN (SELECT id FROM parent_table);
-- Or: DELETE FROM child_table; if truncating all

-- Step 2: Delete parent rows
DELETE FROM parent_table;
COMMIT;

-- Note: DELETE is slower than TRUNCATE for large tables but is transactional
-- Reclaim space after large DELETE
ALTER TABLE parent_table MOVE;
ALTER INDEX pk_parent_table REBUILD;

4. Truncate Child Tables First (Dependency Order)

When you need to empty all tables in a schema, truncate in the correct order (children before parents):

-- Generate TRUNCATE statements in correct dependency order
-- Children first, then parents
SELECT
  'TRUNCATE TABLE ' || table_name || ';' AS truncate_stmt,
  LEVEL AS depth
FROM dba_constraints
WHERE constraint_type IN ('P', 'U')
  AND owner = USER
START WITH table_name NOT IN (
  SELECT DISTINCT p.table_name
  FROM dba_constraints c
  JOIN dba_constraints p ON c.r_constraint_name = p.constraint_name
  WHERE c.constraint_type = 'R' AND c.owner = USER
)
CONNECT BY PRIOR constraint_name = r_constraint_name
ORDER BY LEVEL DESC, table_name;

5. Emergency Procedure: Recreate Constraints After Truncate

If data consistency allows, drop and recreate the foreign key constraints:

-- Save constraint definition first
SELECT
  'ALTER TABLE ' || c.table_name ||
  ' ADD CONSTRAINT ' || c.constraint_name ||
  ' FOREIGN KEY (' || cc.column_name || ')' ||
  ' REFERENCES ' || p.table_name || '(' || pc.column_name || ');' AS recreate_stmt
FROM dba_constraints c
JOIN dba_cons_columns cc ON c.constraint_name = cc.constraint_name AND c.owner = cc.owner
JOIN dba_constraints p  ON c.r_constraint_name = p.constraint_name AND c.r_owner = p.owner
JOIN dba_cons_columns pc ON p.constraint_name = pc.constraint_name AND p.owner = pc.owner
WHERE c.r_owner = USER
  AND p.table_name = 'PARENT_TABLE'
  AND c.constraint_type = 'R';

Prevention Strategies

1. Automate ETL Truncate Order

-- Stored procedure to truncate a set of tables in safe dependency order
CREATE OR REPLACE PROCEDURE truncate_schema_tables AS
BEGIN
  -- Disable all FK constraints in the schema
  FOR c IN (
    SELECT owner, table_name, constraint_name
    FROM dba_constraints
    WHERE constraint_type = 'R' AND owner = USER AND status = 'ENABLED'
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER TABLE ' || c.owner || '.' || c.table_name ||
      ' DISABLE CONSTRAINT ' || c.constraint_name;
  END LOOP;

  -- Truncate all tables
  FOR t IN (SELECT table_name FROM user_tables ORDER BY table_name) LOOP
    EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || t.table_name;
  END LOOP;

  -- Re-enable all FK constraints
  FOR c IN (
    SELECT owner, table_name, constraint_name
    FROM dba_constraints
    WHERE constraint_type = 'R' AND owner = USER AND status = 'DISABLED'
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER TABLE ' || c.owner || '.' || c.table_name ||
      ' ENABLE CONSTRAINT ' || c.constraint_name;
  END LOOP;
END;
/

2. Document Table Dependency Order

• Maintain a schema diagram showing FK parent-child relationships
• Store the dependency-ordered truncate sequence in your runbook
• Use Oracle SQL Developer’s table dependency view to generate the correct order

3. Staging Table Pattern for ETL

• Load new data into staging tables, then swap with the production tables
• Use partition exchange or rename operations to avoid truncate/FK conflicts
• Consider deferrable constraints for ETL windows where order of load is flexible

4. Test Data Management

-- Use DBMS_METADATA to script FK constraints before disabling
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name, owner)
FROM dba_constraints
WHERE constraint_type = 'R'
  AND owner = USER
  AND table_name = 'MY_TABLE';

Related Errors

• ORA-02449 - Unique/primary keys referenced by foreign keys (DROP context)
• ORA-02291 - Integrity constraint violated - parent key not found
• ORA-02292 - Integrity constraint violated - child record found
• ORA-01940 - Cannot DROP user currently connected