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

user_177736 0
Viewed 68694

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.

key oracle
8 Answers

Maybe I misunderstood what Walker asked, but what I understood is : How to find tables that have a foreign key reference to a particular table (ex : EMPLOYEES).

If I try Kupa's answer :

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

I get the tables on which EMPLOYEES have a foreign key reference to.

EMPLOYEES.foreign_key => TABLES.primary_key

See below the updated sql to retrieve the tables that have a foreign key reference to EMPLOYEES.

TABLES.foreign_key => EMPLOYEES.primary_key

select b.table_name "Table Name",
   b.constraint_name "Constraint Name",
   d.table_name "Referenced Table Name",
   d.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 constraint_type='R') b

where d.table_name = 'EMPLOYEES' --your table name instead of EMPLOYEES
and b.r_constraint_name = d.constraint_name;
user_48917240 
SELECT CONSTRAINT_NAME from ALL_CONSTRAINTS WHERE OWNER= sys_context('userenv','current_schema') AND CONSTRAINT_TYPE='R';
user_42690540
Related