Snowflake 校验两表关联列数据类型匹配 生成外键ALTER语句咨询

我们直接在原有查询的关联条件中新增了t2列的类型校验规则，确保匹配到的列和t1的number(38,0)类型完全一致，修改后的完整代码如下：

SELECT
CONCAT('ALTER TABLE "',t2.table_catalog,'"."',
t2.table_schema, '"."',
t2.table_name,
'" ADD FOREIGN KEY (',
t2.column_name,
') REFERENCES "',t1.table_catalog,'"."',
t1.table_schema,'"."',
t1.table_name,
'" (',t1.column_name,');'
)
from information_schema.columns t1
inner join information_schema.columns t2 on
(t2.column_name = CONCAT(REGEXP_REPLACE(t1.table_name,'S$',''),'_ID')
or t2.column_name = CONCAT(REGEXP_REPLACE(t1.table_name,'IES$','Y'),'_ID')
or REPLACE(REPLACE(REPLACE(t2.column_name,'_ID',']]]'),'_',''),']]]','_ID') = CONCAT(REPLACE(t1.table_name,'_',''),'_ID')
or REPLACE(REPLACE(REPLACE(t2.column_name,'_ID',']]]'),'_',''),']]]','_ID') = CONCAT(REPLACE(REGEXP_REPLACE(t1.table_name,'S$',''),'_',''),'_ID')
or REPLACE(REPLACE(REPLACE(t2.column_name,'_ID',']]]'),'_',''),']]]','_ID') = CONCAT(REPLACE(REGEXP_REPLACE(t1.table_name,'IES$',''),'_',''),'_ID')
or t2.column_name = CONCAT(t1.table_name,'_ID')
or REGEXP_REPLACE(t2.column_name,'_C$','') = t1.table_name
or REGEXP_REPLACE(t2.column_name,'_C$','') = REGEXP_REPLACE(t1.table_name,'S$','')
or REGEXP_REPLACE(t2.column_name,'_C$','') = REGEXP_REPLACE(t1.table_name,'IES$','Y')
or REPLACE(REGEXP_REPLACE(t2.column_name,'_C$',''),'_','') = REPLACE(t1.table_name,'_','')
or REPLACE(REGEXP_REPLACE(t2.column_name,'_C$',''),'_','') = REPLACE(REGEXP_REPLACE(t1.table_name,'S$',''),'_','')
or REPLACE(REGEXP_REPLACE(t2.column_name,'_C$',''),'_','') = REPLACE(REGEXP_REPLACE(t1.table_name,'IES$','Y'),'_','')
or REGEXP_REPLACE(t2.column_name,'_C$','') = REGEXP_REPLACE(t1.table_name,'_C$',''))
and t1.table_schema = t2.table_schema
-- 新增类型校验逻辑开始
and t2.data_type = 'NUMBER'
and t2.numeric_precision = 38
and t2.numeric_scale = 0
-- 新增类型校验逻辑结束
and t1.column_name = 'ID'
where t1.table_schema ='WTR'

新增校验规则说明 #

• 只匹配数据类型为NUMBER的t2列
• 强制校验数字精度为38，小数位为0，完全匹配t1的ID字段类型
• 所有原有列名匹配规则完全保留，仅过滤掉类型不匹配的列，避免生成无效的外键语句

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