Azure Synapse Analytics 无主键外键时如何关联事实表与维度表

为什么没有主键/外键强制约束 #

Azure Synapse 专用SQL池（原Azure SQL DW）属于分布式MPP架构的数仓产品，为了保障大规模数据写入、查询的性能，默认不提供强制生效的主键、外键约束，避免额外的约束校验开销拖慢数仓运行效率。

事实表与维度表的关联方案 #

• 首先明确：不需要在数据库层面创建强制外键约束，日常关联完全可以在查询时通过JOIN语句实现，这也是Synapse数仓的标准实践。
• 可选逻辑约束声明：如果需要给数仓元数据、BI工具提供表关联关系的标识，可以创建非强制的主键/外键约束，语法和普通SQL一致，仅需额外添加NOT ENFORCED参数即可，示例如下：
  维度表添加非强制主键：

  ALTER TABLE dim_date 
  ADD CONSTRAINT PK_dim_date_date_id PRIMARY KEY NONCLUSTERED (date_id) NOT ENFORCED;
  

  事实表添加非强制外键：

  ALTER TABLE fact_sales
  ADD CONSTRAINT FK_fact_sales_date_id FOREIGN KEY (date_id) REFERENCES dim_date(date_id) NOT ENFORCED;
  

  注意：这类约束仅作为元数据标记存在，数据库不会对写入的数据做一致性校验，脏数据、关联键不存在的记录依然可以写入，关联数据的一致性需要你在ETL/ELT流程中自行校验。

• 关联性能优化建议：
  • 数据量不大的维度表建议设置为REPLICATE分布类型，每个计算节点都会缓存全量维度数据，JOIN时不需要跨节点拉取数据，性能提升明显
  • 事实表和大维度表的关联键如果使用频率高，可以设置为相同的HASH分布键，JOIN时可以直接在本地节点完成匹配，避免跨节点数据shuffle

常见疑问澄清 #

• 是不是必须创建非强制约束？不是。如果没有BI工具自动识别关联、元数据管理的需求，完全可以只创建基础表结构，有查询需求时手动写JOIN条件关联即可，不会有任何功能问题。
• 怎么保障关联数据的一致性？所有关联键的一致性校验全部放在数据入仓的ELT/ETT链路中完成，比如事实表写入前校验关联的维度键是否已经存在于维度表，不存在的话走未知值兜底逻辑，避免后续关联时出现NULL匹配。

内容的提问来源于stack exchange，提问作者Renato Rezk