// Oracle error reference

ORA-02266: keys referenced by enabled foreign keys

TRUNCATE (or a similar operation) was refused because enabled foreign keys in other tables reference this table's primary or unique key.

Error code
ORA-02266
Category
Constraints and keys
Error message
unique/primary keys in table referenced by enabled foreign keys

What it means

TRUNCATE TABLE removes all rows without checking each one, so Oracle refuses it while an enabled foreign key in another table references the table, even if the child table is empty. A self-referencing foreign key doesn't block it.

Common causes

  • Truncating a parent table that other tables reference.
  • Clearing test data in parent-first order.

How to fix it

  1. Find the referencing constraints.

    SELECT c.owner, c.table_name, c.constraint_name
    FROM all_constraints c
    JOIN all_constraints p ON p.owner = c.r_owner AND p.constraint_name = c.r_constraint_name
    WHERE c.constraint_type = 'R' AND p.table_name = 'CUSTOMERS';
  2. Disable them, truncate, and enable them again (after emptying or fixing the child tables).

    ALTER TABLE orders DISABLE CONSTRAINT orders_customer_fk;
    TRUNCATE TABLE customers;
    ALTER TABLE orders ENABLE CONSTRAINT orders_customer_fk;
  3. On 12c and later, TRUNCATE TABLE customers CASCADE also truncates the child tables, but only when the foreign keys are defined with ON DELETE CASCADE.

  4. Or use DELETE, which checks each row against the foreign keys.

Related errors

More constraints and keys errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.