在Oracle SQL中编写一个猜测外键的脚本

Question

我正在尝试编写一个猜测外键的SQL脚本。我的方法是消除所有不能作为外键的列。剩下的都是体力活。

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

在这一点上，我得到了所有可能匹配的列，但这仍然不够准确。下一步是检查atc1中的每个条目。在atc2中存在Column_name。 如果不是，就不能是外键。如何将该条件添加到where子句中?

方法是:

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;

但这并不像预期的那样工作，因为我不能在查询中使用字符串中的表名。

Answer 1

尝试下面的方法查找现有的外键

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'

对于潜在的外键，这里有一些指针

1. 外键和引用键的数据类型必须相同
2. 外键列和引用键列的值必须相同
3. 子表和父表必须在同一个数据库

对于查询，可以使用下面的

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;