ORA-02449: unique/primary keys referenced by foreign keys
DROP TABLE failed because foreign keys in other tables reference this table's primary or unique key. Drop those constraints first or use CASCADE CONSTRAINTS.
- Error code
ORA-02449- Category
- Constraints and keys
What it means
Oracle won't drop a table while other tables have foreign keys pointing at it, since those constraints would be left referring to nothing. ORA-02449 tells you such constraints exist.
Common causes
- Dropping a parent table that other tables reference.
- Rebuilding a schema in the wrong order.
How to fix it
-
See which constraints reference the table.
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';
-
Drop the table together with the foreign keys that reference it (the child tables and their data stay).
DROP TABLE customers CASCADE CONSTRAINTS;
Related errors
ORA-02292integrity constraint violated - child record foundORA-02291integrity constraint violated - no parent key3726SQL Server: Can't drop: referenced by a FOREIGN KEY1451MySQL: Foreign key fails: cannot delete parent row
More constraints and keys errors
ORA-00001unique constraint violatedORA-01400cannot insert NULL into columnORA-02266keys referenced by enabled foreign keys
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.