In a star schema, are foreign key constraints between facts and dimensions neccessary?

sql-server database-design data-warehouse
Garett picture Garett · May 12, 2010 · Viewed 9k times · Source

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.

Answer

Damir Sudarevic picture Damir Sudarevic · May 12, 2010

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.

Related questions

Database design: one huge table or separate tables?

Currently I am designing a database for use in our company. We are using SQL Server 2008. The database will hold data gathered from several customers. The goal of the database is to acquire aggregate benchmark numbers over several customers. Recently, …

Read More
Difference between clustered and nonclustered index

I need to add proper index to my tables and need some help. I'm confused and need to clarify a few points: Should I use index for non-int columns? Why/why not I've read a lot about clustered and non-clustered …

Read More
How to create materialized views in SQL Server?

I am going to design a DW and I heard about materialized views. Actually I want to create a view and it should update automatically when base tables are changed. Can anyone explain with an query example..

Read More