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
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
-
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';
-
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;
-
On 12c and later,
TRUNCATE TABLE customers CASCADEalso truncates the child tables, but only when the foreign keys are defined withON DELETE CASCADE. -
Or use
DELETE, which checks each row against the foreign keys.
Related errors
ORA-02449unique/primary keys referenced by foreign keysORA-02292integrity constraint violated - child record found1701MySQL: Can't truncate a table with foreign keys
More constraints and keys errors
ORA-00001unique constraint violatedORA-02291integrity constraint violated - no parent keyORA-01400cannot insert NULL into column
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
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.