Hi JR, thanks for responding. It might help to explain the use case. I'm using a data cataloging tool that can reference foreign key definitions to define relationships between data. The catalog doesn't care if the referential integrity is maintained but it really does benefit from having the constraint defined. A second use case is for data discovery. We have a team of data scientists combing through Snowflake and the data lake and having the foreign keys available helps with linking data across tables.
We're using Attunity Replicate for our DDL on the targets (Postgres and Snowflake), and it can create the primary keys but it doesn't have any mechanism for creating foreign keys; the DDL doesn't pick these up. I'm looking at some workaround to identify the foreign keys and create them after Attunity runs its DDL, but it'd be much more convenient to have that as part of the product.
Some data platforms, such as Snowflake, will define a foreign key relationship but will not actually enforce it. PostgreSQL can also be configured to not enforce foreign keys by disabling triggers. This fits my use case perfectly, where I'm replicating data for a data lake use case and I only really need the foreign keys for data discovery.
Does that add some clarity?
Snowflake documentation: https://docs.snowflake.com/en/sql-reference/constraints-overview.html
Postgres documentation (this type of solution would work for our use case): https://stackoverflow.com/questions/38112379/disable-postgresql-foreign-key-checks-for-migrations
