Writing a script to guess foreign keys in Oracle SQL

Question

I am trying to write a SQL script that guesses foreign keys. My approach is to eliminate every column that can't be a foreign key. The rest would be manual work.

SELECT
    atc1.table_name atc1_tn,
    atc1.column_name atc1_cn,
    atc2.table_name atc2_tn,
    atc2.column_name atc2_cn
FROM
    all_tab_cols atc1,
    all_tab_cols atc2
WHERE
        atc1.data_type = 'NUMBER'
    AND atc1.data_type = atc2.data_type
    AND atc1.table_name != atc2.table_name
    AND atc1.high_value <= atc2.high_value
    AND atc1.num_distinct <= atc2.num_distinct

At this point I get all possible matching columns but that is still not accurate enough. The next step would be to check if every entry in atc1.column_name exists in atc2.column_name, because if not it can't be a foreign key.

How can I add that condition to my where clause?

The approach is:

Select
    (execute immediate 'select '||ATC1_CN||' from '||ATC1_TN||'') as a,
    (execute immediate 'select '||ATC2_CN||' from '||ATC2_TN||'') as b
from my_temp_table
where a not in b;

But that doesn't work as expected, because I can't use the table names in a string for a query.

Answer 1

Try the below for existing foreign keys

 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'

For potential foreign keys here are some pointers

1. The data type of foreign and referenced key should be same
2. The values in foreign and referenced key columns should be same
3. The child and parent tables must be on the same database

For query you can use the below

declare
prec number;
begin
for rec in (SELECT atc1.table_name  atc1_tn,
                 atc1.column_name atc1_cn,
                 atc2.table_name  atc2_tn,
                 atc2.column_name atc2_cn
          
            FROM user_tab_cols atc1, user_tab_cols atc2
           WHERE atc1.data_type = 'NUMBER'
             AND atc1.data_type = atc2.data_type
             AND atc1.table_name != atc2.table_name
             AND atc1.high_value <= atc2.high_value
             AND atc1.num_distinct <= atc2.num_distinct
             ) loop
execute immediate 'select count(1) from ' || rec.atc1_tn ||
                  ' a where EXISTS (SELECT 1 FROM ' || rec.atc2_tn ||
                  ' b where  a.' || rec.atc1_cn || '!=' || ' b.' ||
                  rec.atc2_cn || ' )'
  into prec;
if prec = 0 Then
  dbms_output.put_line('potential foreign key rec:table1 ' ||
                       rec.atc1_tn || ' table2: ' || rec.atc2_tn ||
                       ' column1: ' || rec.atc1_cn || ' column2: ' ||
                       rec.atc2_cn);

end if;
end loop;
end;