How to find tables having foreign key to a table in Oracle ?

Question

Hi,

I plan to delete data from a table, i would like to know how many and which tables have a foreign key reference to this particular table in Oracle. As i will have to set the foreign keys to null. I would like to know list of all tables which have a FK to this particular table.

Answer 1

select d.table_name,

       d.constraint_name "Primary Constraint Name",

       b.constraint_name "Referenced Constraint Name"

from user_constraints d,

     (select c.constraint_name,

             c.r_constraint_name,

             c.table_name

      from user_constraints c 

      where table_name='EMPLOYEES' --your table name instead of EMPLOYEES

      and constraint_type='R') b

where d.constraint_name=b.r_constraint_name

Answer 2

There is no need to do this step manually - you can just use a cascading delete.