How to Fix ORA-02449: Unique/Primary Keys in Table Referenced by Foreign Keys in Oracle

ORA-02449 means DROP TABLE was refused because other tables have foreign keys that reference this table's primary or unique key. Dropping it would leave those constraints pointing at nothing. Find the referencing tables, then drop their foreign keys, drop the children too, or use CASCADE CONSTRAINTS.

Reproduce the Error

Example:

create table demo_projects3 (project_id number primary key);
create table demo_tasks3 (task_id number primary key, project_id number references demo_projects3);
drop table demo_projects3;

Output:

Table DEMO_PROJECTS3 created.

Table DEMO_TASKS3 created.

Error starting at line : 3
In command -
drop table demo_projects3
Error report -
ORA-02449: unique/primary keys in table referenced by foreign keys

Fix: Find the References, Then Drop

Example:

-- which tables point at it?
select c.table_name, c.constraint_name
from   user_constraints c
join   user_constraints p on p.constraint_name = c.r_constraint_name
where  p.table_name = 'DEMO_PROJECTS3';

drop table demo_projects3 cascade constraints;     -- drops the foreign keys, not the child table
select table_name from user_tables where table_name like 'DEMO_%3';

Output:

TABLE_NAME     CONSTRAINT_NAME
______________ __________________
DEMO_TASKS3    SYS_C0018425

Table DEMO_PROJECTS3 dropped.

TABLE_NAME
______________
DEMO_TASKS3

DEMO_TASKS3 references the table through a system-named constraint. DROP TABLE ... CASCADE CONSTRAINTS drops that foreign key along with the table, but the child table and its rows stay.

Things to Know

• Use CASCADE CONSTRAINTS only when the relationship is meant to end; otherwise drop the child tables first.
• Truncating a referenced table fails for a similar reason, with ORA-02266, unless TRUNCATE ... CASCADE is used with ON DELETE CASCADE keys.
• DROP TABLE ... PURGE skips the recycle bin; without it, a dropped table can be restored with FLASHBACK TABLE.

Related Guides

• How to Fix ORA-02292: Integrity Constraint Violated - Child Record Found in Oracle
• How to Restore Dropped Tables from the Recycle Bin in Oracle

Conclusion

ORA-02449 means other tables still reference the table you want to drop. List them through USER_CONSTRAINTS, then drop their foreign keys or the child tables, or drop with CASCADE CONSTRAINTS when the relationship should go.