CODEWITHSUNDEEP
snowflake-cloud-data-platform
KristiLuna
KristiLuna

Reputation: 1903

Snowflake - Drop all Foreign Keys in Schema, possibly using CTE to delete

I know I can do the below command it will drop the FK's for that table but I want to make sure all FK's have been dropped in all tables based on a schema:

ALTER TABLE fk_table DROP FOREIGN KEY (fk_column);

If there isn't an easier way I found if I run the below it will show me all FK's created for a schema, but then how do I delete everything found in the results below:

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;

Upvotes: 2

Views: 151

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175874

Generating ALTER statement to remove foreign keys:

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;

The generated alter_query should be copied into worksheet and run. If the goal is to have more "automatic" approach then it should be wrapped with stored procedure.

Sidenote: Foreign keys are NOT enforced on Snowflake. It means that is possible to:

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!

If the goal of foreign key removal is to improve perfromance there is no gain from that operation.

Related: Supported Constraint Types

Upvotes: 2

Related Questions