Thursday, August 2, 2012

Oracle DB: find table holding foreign key constraint on another table

sometimes you want to truncate table BLA, and you get an erro message such as
"unable to delete because of some foreign key constraint".... and you don't have a clue which table is pointing to BLA...

here is how to find out:

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


Make sure you use the right CAPS.
(thanks to stackoverflow)

This is useful if you get a "ORA-02266: unique/primary keys in table referenced by enabled foreign keys".

No comments: