Who is referencing my table ??

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
// command:
// 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.

Advertisements

One thought on “Who is referencing my table ??

  1. Pingback: Index « My confrontations with oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s