// Oracle error reference

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
Error message
unique/primary keys in table referenced by foreign 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

  1. 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';
  2. 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

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.