Foreign Key Constraints

Hi

What is the norm in data warehouses when implementing FK constraints, is it beneficial to create them or not have them at all
(SQL Server 2008)

Regards

Tim

Defining the foreign keys on the fact table is supposed to help the star schema optimizer in SQL Server 2008. Without the constraints, the optimizer decides on it's own which table is the fact table and which is a dimension table.

If you define the foreign keys, turn off the referential integrity otherwise it will bring your load to a standstill.

Some say that you should turn the referential integrity back on after the load to prevent developers from doing something they shouldn't do with the dimension tables. Some say leave it off. Personally, turn the referential integrity on between loads - the added protection doesn't hurt.

In general, there is no benefit in enforcing RI through constrains in a DW built using surrogate keys. The key assignment process itself enforces the constraint making DB level enforcement redundant. DB level enforcement can significantly impact load times, often by an order of magnitude.

As Jeff pointed out, some, but not all, database systems use the declaration of a FK relationship to help its optimizer choose the appropriate query approach. However, declaration and enforcement are two different things. There is no reason to do the latter.

I was at a recent class offerred by the Kimball Group. During the class, the instructor suggested that turning referential integreity on after the load had some benefit - could prevent a piece of code that had not been throroughly tested from deleting rows in a dimension table that are in use in fact tables or changing dimension key values in fact tables to values that don't exist. Is this rare? Yes. Can it happen? Yes.

I don't recall what the down side to turning off referential integrity before the load and turning it back on after the load was.

But the worst thing is to leave enforcement on during the load - that can bring the load to it's knees.