Foreign Key Constraints
3 posters
Page 1 of 1
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
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
Re: Foreign Key Constraints
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.
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
Re: Foreign Key Constraints
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.
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.
Re: Foreign Key Constraints
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.
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
![-](https://2img.net/i/empty.gif)
» Foreign Key Constraints in Data Mart Design
» Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server
» Constraints on Dimensions
» Constraints on Surrogate Keys?
» Constraints make a bad performance, but why?
» Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server
» Constraints on Dimensions
» Constraints on Surrogate Keys?
» Constraints make a bad performance, but why?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|