Foreign Key constraints Synapse Azure

Question

Am currently creating a datawarehouse in Azure Synapse, however Synapse does not allow for the creation of foreign keys. This is vital for referential integrity between the fact and dimension table. Does anyone have any suggestions as to what the alternatives are in synapse to enforce a PK FK relationship?

Opsis

Answer 1

I searched about this topic and I found that the focus of Synapse is performance and not integrity reinforcement. We can create primary keys and structure the star schema with fact, dimensions and code join tables between them.

It was confused me too until I make this tutorial and read this carefully.

Load Contoso retail data to Synapse SQL

Leonard

Answer 2

In a star schema any referential integrity should be enforced within the ETL tool used to load the data and not in the DB itself. Some DBs support logical FKs that can help with query execution plans but they should never be physicalised

Stereometry