如何在Oracle SQL猜外键脚本中添加全值存在校验条件？

你需要优化外键猜测脚本，在现有筛选逻辑基础上，添加校验源列所有值都存在于目标列的条件，以下是可行的实现方案：

原查询的局限 #

你当前的SQL已经筛选出数据类型匹配、值域范围匹配的列对，但未验证源列的所有值是否都能在目标列中找到——这是外键的核心前提之一。你尝试的动态SQL写法不符合Oracle静态SQL的语法规则，无法直接执行。

方案1：自定义校验函数（推荐） #

先创建一个函数，封装“检查源列所有值是否存在于目标列”的逻辑：

CREATE OR REPLACE FUNCTION check_all_values_exist(
    p_source_table  IN VARCHAR2,
    p_source_col    IN VARCHAR2,
    p_target_table  IN VARCHAR2,
    p_target_col    IN VARCHAR2
) RETURN BOOLEAN IS
    v_missing_count NUMBER;
BEGIN
    -- 统计源列中不在目标列中的值的数量
    EXECUTE IMMEDIATE 
        'SELECT COUNT(*) 
         FROM "' || p_source_table || '" 
         WHERE "' || p_source_col || '" NOT IN (SELECT "' || p_target_col || '" FROM "' || p_target_table || '")'
    INTO v_missing_count;

    -- 没有缺失值则返回TRUE
    RETURN v_missing_count = 0;
EXCEPTION
    WHEN OTHERS THEN
        -- 处理表/列不存在、权限不足等异常，返回FALSE排除该列对
        RETURN FALSE;
END;
/

然后修改原查询，在WHERE子句中调用该函数：

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
    -- 添加全值存在校验
    AND check_all_values_exist(atc1.table_name, atc1.column_name, atc2.table_name, atc2.column_name)

方案2：XMLQUERY动态校验（无需创建函数） #

如果不想创建函数，可借助Oracle的XMLQUERY功能执行动态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
    AND XMLQUERY(
        'let $cnt := sql:execute("SELECT COUNT(*) FROM &quot;' || atc1.table_name || '&quot; WHERE &quot;' || atc1.column_name || '&quot; NOT IN (SELECT &quot;' || atc2.column_name || '&quot; FROM &quot;' || atc2.table_name || '&quot;)")
         return $cnt'
        RETURNING CONTENT
    ).getNumberVal() = 0

注意事项 #

• 确保执行用户拥有访问all_tab_cols及所有涉及表数据的权限。
• 表名/列名含特殊字符时，用双引号包裹可避免语法错误（上述两种方案已处理）。
• 大数据量表的校验会消耗较多资源，建议先通过原条件过滤出少量候选列对，再执行全值校验。

内容的提问来源于stack exchange，提问作者Philipp Loibl