问题描述
我第一次接触到数据仓库，我想知道是否有必要在事实和维度之间有外键限制。没有任何重大的缺点吗？我目前正在使用关系型星型模式。在传统应用中，我习惯了这些应用程序，但是我开始怀疑在这种情况下是否需要它们。我正在一个SQL Server 2005环境中工作。更新：对于那些有兴趣的人，我遇到了一个问同样的问题。
大多数数据仓库（DW）没有外键实现为约束，因为：
-
一般来说，外键约束将触发：从维表中插入事实表，任何键更新和删除。
-
在加载过程中，删除索引和约束以加快加载过程，数据完整性由ETL应用程序执行。
-
加载表后，DW基本上是只读的 - 约束在读取时不会触发。
-
任何所需的索引在加载后重新构建。
-
在DW中删除是一个受控制的过程。在从维中删除行之前，将查询要删除的行的键的事实表 - 仅当这些键在任何事实表中不存在时才允许删除。
为防万一，定期运行查询以检测事实表中的孤立记录是常见的。
I'm getting my first exposure to data warehousing, and I’m wondering is it necessary to have foreign key constraints between facts and dimensions. Are there any major downsides for not having them? I’m currently working with a relational star schema. In traditional applications I’m used to having them, but I started to wonder if they were needed in this case. I’m currently working in a SQL Server 2005 environment.
UPDATE: For those interested I came across a poll asking the same question.
Most data-warehouses (DW) do not have foreign keys implemented as constraints, because:
In general, foreign key constraint would trigger on: an insert into a fact table, any key-updates, and a delete from a dimension table.
During loading, indexes and constraints are dropped to speed-up the loading process, data integrity is enforced by the ETL application.
Once tables are loaded, DW is essentially read-only -- the constraint does not trigger on reads.
Any required indexes are re-built after the loading.
Deleting in a DW is a controlled process. Before deleting rows from dimensions, fact tables are queried for keys of rows to be deleted -- deleting is allowed only if those keys do not exists in any of fact tables.
Just in case, it is common to periodically run queries to detect orphan records in fact tables.
这篇关于在星型模式中，事实和维度之间的外键约束是必需的吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！