We have a lot of tables in Snowflake, none had Primary Keys (I’ve just added them in) but now I’m feeling overwhelmed trying to figure out how to create Foreign Keys without going through each table to see the link between data in two tables? Is there a script that I can create to even do this?
P.S. Below is one created to check existing PK’s and if it doesn’t exist it shows the command to run to create, if this helps anyone:
select distinct
CONCAT(
'ALTER TABLE "',table_catalog, '"."',
table_schema,'"."',
table_name,
'" ADD PRIMARY KEY (',
column_name,
');'
)
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'
)
Advertisement
Answer
The typical reason for adding constraints to Snowflake are to simplify JOINs in BI tools and general documentation. If you have ERWin or similar tool, and you data has good naming conventions, reverse engineering typically has an option to infer constraints from column names. Best solution is to pull the meta-data from the original source (assuming it existed there).