Today my developers were puzzled at ORA-02449 while trying to drop a table.
$ oerr ora 02449
02449, 00000, “unique/primary keys in table referenced by foreign keys”
// *Cause: An attempt was made to drop a table with unique or
// primary keys referenced by foreign keys
// *Action: Before performing the above operations the table, drop the
// foreign key constraints in other tables. You can see what
// constraints are referencing a table by issuing the following
// SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = “tabnam”;
They thought that they could drop the parent table if the child tables are empty and it did not work.
Jeniffer in her page has mentioned on how to go about when the question ‘who is referencing my table?’ comes to your mind.
select 'alter table '||a.owner||'.'||a.table_name|| ' disable constraint '||a.constraint_name||';' from all_constraints a, all_constraints b where a.constraint_type = 'R' and a.r_constraint_name = b.constraint_name and a.r_owner = b.owner and b.table_name = '<table to be dropped>';
Finally they had to drop the Foreign Keys of the Child tables before dropping the parent table.