Finding Foreign Key Relationships

It's useful to know the foreign keys and the unique or primary keys to which they relate. Foreign keys produce potentially damaging locking problems if the foreign key columns on the child table are not indexed. The first query below lists all of the foreign keys and the parent table and columns to which they relate. 

# forgnkey.sql
SELECT  a.owner , a.table_name , c.column_name ,
        b.owner , b.table_name , d.column_name
FROM    dba_constraints a, dba_constraints b,
        dba_cons_columns c, dba_cons_columns d
WHERE   a.r_constraint_name = b.constraint_name
  AND   a.constraint_type = 'R'
  AND   b.constraint_type = 'P'
  AND   a.r_owner=b.owner
  AND   a.constraint_name = c.constraint_name
  AND   b.constraint_name=d.constraint_name
  AND   a.owner = c.owner
  AND   a.table_name=c.table_name
  AND   b.owner = d.owner
  AND   b.table_name=d.table_name;

The second query lists all of the foreign keys that do not have the appropriate indexes in place on the child table. It shows the foreign key constraints that cause locking problems.

# forgnkey.sql
SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
        ||'['||acc.position||'])'||' ***** Missing Index'
  FROM  all_cons_columns acc, all_constraints ac
 WHERE  ac.constraint_name = acc.constraint_name
   AND  ac.constraint_type = 'R'
   AND  (acc.owner, acc.table_name, acc.column_name, acc.position)
             IN
 (SELECT acc.owner, acc.table_name, acc.column_name, acc.position
    FROM   all_cons_columns acc, all_constraints ac
   WHERE  ac.constraint_name = acc.constraint_name
     AND   ac.constraint_type = 'R'
  MINUS
  SELECT table_owner, table_name, column_name, column_position
    FROM all_ind_columns)
ORDER BY acc.owner, acc.constraint_name,
         acc.column_name, acc.position;