Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Finding table containing foreign key constraint

488972Jan 11 2007 — edited Jan 18 2007
I am trying to truncate a table, but am getting the error:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

My problem is finding the table that references this one that I am trying to truncate. Someone gave me the following query, but it does not give me the table I am looking for:

select r.owner, r.table_name
from all_constraints r, all_constraints o
where r.r_owner = o.owner and r.r_constraint_name = o.constraint_name
and o.constraint_type in ('P','U') and r.constraint_type = 'R'
and o.table_name = 'mytable';

There must be a simple query to find the table that contains the foreign key, but I can't find it now. Does someone have a query I can use for this ?

TIA
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 15 2007
Added on Jan 11 2007
7 comments
787 views