Foreign Key Constraints

View previous topic View next topic Go down

Foreign Key Constraints

Post  tim_goodsell on Tue Oct 12, 2010 2:52 am

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

tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: Foreign Key Constraints

Post  Jeff Smith on Tue Oct 12, 2010 10:16 am

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Foreign Key Constraints

Post  ngalemmo on Tue Oct 12, 2010 12:02 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Foreign Key Constraints

Post  Jeff Smith on Wed Oct 13, 2010 10:23 am

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Foreign Key Constraints

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum