how to find foreign key dependencies pointing to one record in oracle

how to find foreign key dependencies pointing to one record in oracle  using -'database,oracle,database-schema'

I have a very large Oracle database, with many many tables and millions of rows. I need to delete one of them, but want to make sure that dropping it will not break any other dependent rows that point to it as a foreign key record. Is there a way to get a list of all the other records, or at least table schemas, that point to this row? I know that I could just try to delete it myself, and catch the exception, but I won't be running the script myself and need it to run clean the first time through.

I have the tools SQL Developer from Oracle, and PL/SQL Developer from AllRoundAutomations at my disposal.

Thanks in advance!

asked Sep 15, 2015 by JuanPalfreym
0 votes

6 Answers

0 votes

I always look at the Foreign keys for the starting table and work my way back. The DB tools usually have a dependencies or constraints node. I know L/SQL Developer has a way to see FK's, but it's been a while since I have used it, so I can't explain it...

just replace XXXXXXXXXXXX with a table name...

/* The following query lists all relationships */ 

 a.owner||'.'||a.table_name "Referenced Table"
,b.owner||'.'||b.table_name "Referenced by"
,b.constraint_name "Foreign Key"
from all_constraints a, all_constraints b 
b.constraint_type = 'R'
and a.constraint_name = b.r_constraint_name 
and b.table_name='XXXXXXXXXXXX' -- Table name 
order by a.owner||'.'||a.table_name
answered Sep 15, 2015 by LeifFenneri
0 votes

Here is my solution to list all references to a table:

select src_cc.owner as src_owner, src_cc.table_name as src_table, src_cc.column_name as src_column,
dest_cc.owner as dest_owner, dest_cc.table_name as dest_table, dest_cc.column_name as dest_column,
from all_constraints c
inner join all_cons_columns dest_cc on c.r_constraint_name = dest_cc.constraint_name and c.r_owner = dest_cc.owner
inner join all_cons_columns src_cc on c.constraint_name = src_cc.constraint_name and c.owner = src_cc.owner
where c.constraint_type = 'R'
and dest_cc.owner = 'MY_TARGET_SCHEMA'
and dest_cc.table_name = 'MY_TARGET_TABLE'
--and dest_cc.column_name = 'MY_OPTIONNAL_TARGET_COLUMN'

With this solution you also have the information of which column of which table is referencing which column of your target table (and you can filter on it).

answered Sep 15, 2015 by ChrWalsh
0 votes

We can use the data dictionary to identify the tables which reference the primary key of the table in question. From that we can generate some dynamic SQL to query those tables for the value we want to zap:

SQL> declare
  2      n pls_integer;
  3      tot pls_integer := 0;
  4  begin
  5      for lrec in ( select table_name from user_constraints
  6                    where r_constraint_name = 'T23_PK' )
  7      loop
  8          execute immediate 'select count(*) from '||lrec.table_name
  9                              ||' where col2 = :1' into n using &&target_val;
 10          if n = 0 then
 11              dbms_output.put_line('No impact on '||lrec.table_name);
 12          else
 13              dbms_output.put_line('Uh oh! '||lrec.table_name||' has '||n||' hits!');
 14          end if;
 15          tot := tot + n;
 16      end loop;
 17      if tot = 0
 18      then
 19          delete from t23 where col2 = &&target_val;
 20          dbms_output.put_line('row deleted!');
 21      else
 22          dbms_output.put_line('delete aborted!');
 23      end if;
 24  end;
 25  /
Enter value for target_val: 6
No impact on T34
Uh oh! T42 has 2 hits!
No impact on T69
delete aborted!

PL/SQL procedure successfully completed.


This example cheats a bit. The name of the target primary key is hardcoded, and the referencing column has the same name on all the dependent tables. Fixing these issues is left as an exercise for the reader ;)

answered Sep 15, 2015 by LinneaXADGjp
0 votes

I had a similar problem recently, but experienced soon, that finding the direct dependencies is not enough. So I wrote a query to show a tree of multilevel foreign key dependencies:

SELECT LPAD(' ',4*(LEVEL-1)) || table1 || ' <-- ' || table2 tables, table2_fkey
  (SELECT a.table_name table1, b.table_name table2, b.constraint_name table2_fkey
  FROM user_constraints a, user_constraints b 
  WHERE a.constraint_type IN('P', 'U') 
  AND b.constraint_type = 'R' 
  AND a.constraint_name = b.r_constraint_name 
  AND a.table_name != b.table_name
  AND b.table_name <> 'MYTABLE')
CONNECT BY PRIOR  table2 = table1 AND LEVEL <= 5

It gives a result like this, when using SHIPMENT as MYTABLE in my database:

answered Sep 15, 2015 by JaiPlain
0 votes

I was surprised at how difficult it was to find the dependency order of tables based on foreign key relationships. I needed it because I wanted to delete the data from all tables and import it again. Here is the query I wrote to list the tables in dependency order. I was able to script the deletes using the query below, and import again using the results of the query in reverse order.

   SELECT referenced_table
         ,MAX(lvl) for_deleting
         ,MIN(lvl) for_inserting
         ( -- Hierarchy of dependencies
         SELECT LEVEL lvl
               ,t.table_name referenced_table
               ,b.table_name referenced_by
         FROM user_constraints A
         JOIN user_constraints b 
               ON  A.constraint_name = b.r_constraint_name
               and b.constraint_type = 'R'
         RIGHT JOIN user_tables t
               ON  t.table_name = A.table_name
         START WITH b.table_name IS NULL
         CONNECT BY b.table_name = PRIOR t.table_name
   GROUP BY referenced_table
   ORDER BY for_deleting, for_inserting;
answered Sep 15, 2015 by AntonioDelat
0 votes

Oracle constraints uses Table Indexes to reference data.
To find out what tables are referencing one table, just look for index in reverse order.

/* Toggle ENABLED and DISABLE status for any referencing constraint: */ 

select 'ALTER TABLE '||b.owner||'.'||b.table_name||' '||
        decode(b.status, 'ENABLED', 'DISABLE ', 'ENABLE ')||
       'CONSTRAINT '||b.constraint_name||';' 
  from all_indexes a,
       all_constraints b
 where a.table_name='XXXXXXXXXXXX' -- Table name 
   and a.index_name = b.r_constraint_name;

Obs.: Disabling references improves considerably the time of DML commands (update, delete and insert).

This can help a lot in bulk operations, where you know that all data is consistent.

/* List which columns are referenced in each constraint */

select ' TABLE "'||b.owner||'.'||b.table_name||'"'||
        '('||listagg (c.column_name, ',') within group (order by c.column_name)||')'|| 
        ' FK "'||b.constraint_name||'" -> '||a.table_name||
        ' INDEX "'||a.index_name||'"'
  from all_indexes a,
       all_constraints b,
       all_cons_columns c
 where rtrim(a.table_name) like 'XXXXXXXXXXXX' -- Table name 
   and a.index_name = b.r_constraint_name
   and c.constraint_name = b.constraint_name
 group by b.owner, b.table_name, b.constraint_name, a.table_name, a.index_name
 order by 1;
answered Sep 15, 2015 by Tanisha67Qd