SnowflakeDrop架构中的所有外键，可能使用CTE删除

Question

我知道我可以执行以下命令，它将删除该表的FK，但我想确保所有表中的所有FK都已基于架构删除：

ALTER TABLE fk_table DROP FOREIGN KEY (fk_column);

如果没有更简单的方法，如果我运行下面的程序，它会显示为架构创建的所有FK，但我如何删除下面结果中的所有内容：

select fk_tco.table_schema as foreign_schema,
fk_tco.table_name as foreign_table,
fk_tco.constraint_name as foreign_constraint,
'>-' as rel,
pk_tco.table_schema as referenced_schema,
pk_tco.table_name as referenced_table,
pk_tco.constraint_name as referenced_constraint
from information_schema.referential_constraints rco
join information_schema.table_constraints fk_tco 
on fk_tco.constraint_name = rco.constraint_name
and fk_tco.constraint_schema = rco.constraint_schema
join information_schema.table_constraints pk_tco
on pk_tco.constraint_name = rco.unique_constraint_name
and pk_tco.constraint_schema = rco.unique_constraint_schema
where pk_tco.table_schema = 'SCHEMA_TO_DELETE_IN'     
order by fk_tco.table_schema,
fk_tco.table_name;

Answer 1

生成ALTER语句以删除外键：

select 'ALTER TABLE ' || fk_tco.table_schema || '.' || fk_tco.table_name ||
' DROP CONSTRAINT "' || fk_tco.constraint_name || '";' AS alter_query
from information_schema.referential_constraints rco
join information_schema.table_constraints fk_tco 
on fk_tco.constraint_name = rco.constraint_name
and fk_tco.constraint_schema = rco.constraint_schema
join information_schema.table_constraints pk_tco
on pk_tco.constraint_name = rco.unique_constraint_name
and pk_tco.constraint_schema = rco.unique_constraint_schema
where pk_tco.table_schema = 'SCHEMA_TO_DELETE_IN'     
order by fk_tco.table_schema,
fk_tco.table_name;

生成的alter_query应复制到工作表中并运行。如果目标是拥有更多的"；"自动"；方法，则应该使用存储过程对其进行包装。

---

旁注：Snowflake上不强制执行外键。这意味着有可能：

CREATE TABLE t(id INT PRIMARY KEY);
CREATE TABLE x(col TEXT, id INT REFERENCES t(id));
INSERT INTO x(col, id) VALUES ('a', 1); -- please note t is empty!

如果去除外键的目的是改善背信弃义，那么这种操作就没有任何好处。

相关：支持的约束类型