Oracle ORA-02266 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

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

ORA-02266 occurs when you attempt to perform a DDL operation—such as TRUNCATE TABLE, DROP TABLE, or disabling a primary key constraint—on a parent table whose primary or unique key is still referenced by an enabled foreign key in a child table. Oracle enforces this restriction to protect referential integrity. Until the referencing foreign key constraints are handled, the operation will be blocked.

Top 3 Causes

1. TRUNCATE TABLE Blocked by Active Foreign Keys

Unlike DELETE, TRUNCATE is a DDL statement and cannot be rolled back, so Oracle enforces stricter referential integrity checks. If any child table has an enabled FK pointing to the parent, the truncate fails immediately.

-- This will raise ORA-02266 if child_table has an active FK
TRUNCATE TABLE parent_table;

-- Fix: Disable the FK first, truncate, then re-enable
ALTER TABLE child_table DISABLE CONSTRAINT fk_child_parent;
TRUNCATE TABLE parent_table;
ALTER TABLE child_table ENABLE CONSTRAINT fk_child_parent;

2. DROP TABLE Without CASCADE CONSTRAINTS

Dropping a parent table while child tables still hold enabled foreign key references will trigger ORA-02266. This commonly happens during schema redesigns or migrations when dependency order is not respected.

-- This will fail if FK references exist
DROP TABLE parent_table;

-- Fix: Use CASCADE CONSTRAINTS to auto-drop referencing FKs
-- Note: child TABLE is NOT dropped, only the FK constraints are removed
DROP TABLE parent_table CASCADE CONSTRAINTS;

-- Check what FKs reference your table before dropping
SELECT c.table_name    AS child_table,
       c.constraint_name AS fk_name
  FROM user_constraints c
  JOIN user_constraints p
    ON c.r_constraint_name = p.constraint_name
 WHERE p.table_name       = 'PARENT_TABLE'
   AND c.constraint_type  = 'R';

3. Disabling or Dropping a Primary Key With Active Child FKs

Attempting to disable or drop the primary key on a parent table when child FKs are still enabled will also produce this error. The CASCADE option on ALTER TABLE resolves this cleanly.

-- This will fail if a child FK is enabled
ALTER TABLE parent_table DISABLE PRIMARY KEY;

-- Fix: Use CASCADE to disable child FKs simultaneously
ALTER TABLE parent_table DISABLE PRIMARY KEY CASCADE;

-- Re-enable PK after your work is done
ALTER TABLE parent_table ENABLE PRIMARY KEY;

-- Then re-enable child FK constraints individually or via dynamic SQL
BEGIN
  FOR r IN (
    SELECT c.table_name, c.constraint_name
      FROM user_constraints c
      JOIN user_constraints p
        ON c.r_constraint_name = p.constraint_name
     WHERE p.table_name      = 'PARENT_TABLE'
       AND c.constraint_type = 'R'
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER TABLE ' || r.table_name ||
      ' ENABLE CONSTRAINT ' || r.constraint_name;
  END LOOP;
END;
/

Quick Fix Summary

Scenario	Solution
`TRUNCATE` blocked	Disable FK → Truncate → Enable FK
`DROP TABLE` blocked	Use `CASCADE CONSTRAINTS`
`DISABLE PRIMARY KEY` blocked	Use `DISABLE PRIMARY KEY CASCADE`

Prevention Tips

1. Always audit foreign key dependencies before DDL operations.
Run a dependency check query against user_constraints or dba_constraints before any DDL on a parent table. Make this a mandatory step in your deployment checklist.

-- Quick dependency check
SELECT c.table_name, c.constraint_name, c.status
  FROM user_constraints c
  JOIN user_constraints p ON c.r_constraint_name = p.constraint_name
 WHERE p.table_name = 'YOUR_TABLE'
   AND c.constraint_type = 'R';

2. Standardize a FK-safe data purge script for recurring batch jobs.
For any recurring truncate or bulk-delete operation, wrap the logic in a reusable script that handles FK disable/enable automatically. This eliminates ORA-02266 as a runtime surprise and keeps your operations clean and auditable.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community