如何确定Snowflake现有表中需要创建及关联的Foreign Keys？

前提说明 #

• 脚本依赖通用的表字段命名惯例：子表中关联父表的外键列命名为{父表名}_ID（和你之前设置主键时统一用ID作为主键字段的逻辑匹配）
• 仅适用于单库单Schema下的表批量处理，多Schema场景可自行调整匹配逻辑

批量生成外键创建SQL脚本 #

SELECT DISTINCT
    CONCAT(
        'ALTER TABLE "', c.table_catalog, '"."', c.table_schema, '"."', c.table_name, '" ',
        'ADD FOREIGN KEY (', c.column_name, ') ',
        'REFERENCES "', c.table_catalog, '"."', c.table_schema, '"."', 
        -- 提取父表名：去掉外键列末尾的'_ID'后缀
        REPLACE(UPPER(c.column_name), '_ID', ''),
        '"(ID);'
    ) AS create_foreign_key_sql
FROM information_schema.columns c
-- 替换为你的目标Schema名
WHERE c.table_schema = 'SCHEMA_NAME_HERE'
-- 匹配所有后缀为_ID且不是当前表主键的列（排除自表主键）
AND c.column_name LIKE '%\_ID' ESCAPE '\'
AND c.column_name != 'ID'
-- 过滤已存在的外键，避免重复创建
AND NOT EXISTS (
    SELECT 1 
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
        ON tc.constraint_name = kcu.constraint_name
        AND tc.table_schema = kcu.table_schema
    WHERE tc.constraint_type = 'FOREIGN KEY'
        AND tc.table_schema = c.table_schema
        AND tc.table_name = c.table_name
        AND kcu.column_name = c.column_name
)
-- 验证匹配到的父表真实存在且有ID主键
AND EXISTS (
    SELECT 1 
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
        ON tc.constraint_name = kcu.constraint_name
        AND tc.table_schema = kcu.table_schema
    WHERE tc.constraint_type = 'PRIMARY KEY'
        AND tc.table_schema = c.table_schema
        AND tc.table_name = REPLACE(UPPER(c.column_name), '_ID', '')
        AND kcu.column_name = 'ID'
)

注意事项 #

• 脚本生成后建议先抽验10%~20%的生成语句，确认关联关系符合业务逻辑，再批量执行
• Snowflake默认创建的外键为NOT ENFORCED模式，仅用于元数据标记和查询优化器分析，不会校验数据一致性；如果需要强制校验外键约束，需要在创建语句末尾追加ENFORCED参数，同时注意开启后会影响写入性能
• 如果你的外键命名规则不是{父表名}_ID，可以调整REPLACE(UPPER(c.column_name), '_ID', '')部分的逻辑适配你的命名规范

参考：主键批量创建脚本（格式化后版本） #

SELECT DISTINCT
    CONCAT(
        'ALTER TABLE "',table_catalog, '"."',
        table_schema,'"."',
        table_name,
        '" ADD PRIMARY KEY (',
        column_name,
        ');'
    ) AS create_primary_key_sql
FROM information_schema.columns c
WHERE table_schema = 'SCHEMA_NAME_HERE'
AND column_name = 'ID'
AND NOT EXISTS (
    SELECT 1 FROM
    information_schema.table_constraints
    WHERE constraint_schema = c.table_schema
    AND table_name = c.table_name
    AND constraint_type = 'PRIMARY KEY'
)

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