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

Question

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
  FK.OWNER||'.'||FK.TABLE_NAME AS CHILD_TABLE,
  SRC.OWNER||'.'||SRC.TABLE_NAME AS PARENT_TABLE,
  FK.CONSTRAINT_NAME AS FK_CONSTRAINT,
  SRC.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT
FROM ALL_CONSTRAINTS FK
JOIN ALL_CONSTRAINTS SRC ON FK.R_CONSTRAINT_NAME = SRC.CONSTRAINT_NAME
WHERE
  FK.CONSTRAINT_TYPE = 'R'
  AND SRC.OWNER = 'MY_SCHEMA'
  AND SRC.TABLE_NAME = 'MY_TABLE';

I have a situation where the table I'm interested in isn't owned by the schema I was connecting as. So I needed to modify the query in the currently accepted answer to use ALL_CONSTRAINTS instead of USER_CONSTRAINTS. In the process, I made a mistake, and I found the accepted answer to be very difficult to read so that I could fix it. (The lack of explanation didn't help.) As a result, I ended up coming up with my own query. It's basically the same, but I think it's a bit easier to grok.

FK.CONSTRAINT_TYPE = 'R' filters down FK to a set of foreign key constraints, and the join pairs these foreign keys up with their "Referenced constraint". (The referenced constraint is usually the primary key of the "parent" table.) Finally, we filter down to the parent table we're interested in using SRC.OWNER = 'MY_SCHEMA' AND SRC.TABLE_NAME = 'MY_TABLE'.

Naturally, you can switch this to use USER_CONSTRAINTS if you wish; just remove the SRC.OWNER check and the OWNER prefixes in the SELECT.

Answer 2

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 3

Below query will give all the foreign key constraints defined on TABLE_NAME:

select baseTable.* from all_constraints baseTable , all_constraints referentedTable 
    where baseTable.R_CONSTRAINT_NAME = referentedTable.CONSTRAINT_NAME
    and baseTable.constraint_type = 'R'
    and referentedTable.table_name = 'TABLE_NAME';

Answer 4

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;

Answer 5

If you also need the fields to be included:

select b.table_name      "Referencing Table", 
       b.CONSTRAINT_NAME "Referencing Constraint",
       (select wm_concat(column_name)
          from all_cons_columns
         where owner = b.owner
           and constraint_name = b.CONSTRAINT_NAME
       ) "Referencing Columns",
       a.CONSTRAINT_NAME         "Referenced Constraint",
       (select wm_concat(column_name)
          from all_cons_columns
         where owner = a.owner
           and constraint_name = a.CONSTRAINT_NAME
       ) "Referenced columns"
  from all_constraints a,
       all_constraints b
 where a.owner = b.r_owner
   and a.owner = '<<OWNER>>'
   and a.table_name = '<<TABLE_NAME>>'
   and a.constraint_type in ('P', 'U')
   and b.constraint_type = 'R'
   and b.R_CONSTRAINT_NAME = a.constraint_name

Answer 6

SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 
       -- referenced pk
       c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
                        AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                           AND c.r_constraint_name = c_pk.constraint_name
 WHERE c.constraint_type = 'R'
   AND a.table_name = :TableName

Answer 7

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

Answer 8

SELECT CONSTRAINT_NAME from ALL_CONSTRAINTS WHERE OWNER= sys_context('userenv','current_schema') AND CONSTRAINT_TYPE='R';