Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Constraints on Surrogate Keys?

3 posters

Go down

Constraints on Surrogate Keys? Empty Constraints on Surrogate Keys?

Post  ebry74 Wed Mar 20, 2013 2:26 pm

What is the best practice for setting foreign key constraints in the dimensional model? Should I define the surrogate key as a primary key in the dimensions, and as a foreign key in the fact, or not?

Thanks in advance!

ebry74

Posts : 5
Join date : 2011-06-20

Back to top Go down

Constraints on Surrogate Keys? Empty Re:Constraints on Surrogate Keys?

Post  hkandpal Wed Mar 20, 2013 8:51 pm

Hi,

putting a foreign key on a fact table will help as it will put a check where the data from a dimensin will not be deleted by accident if it is being used in a Fact table. It may help in query performance only drawback when trying to insert in the fact it will slow down the performance.


thanks

hkandpal

Posts : 113
Join date : 2010-08-16

Back to top Go down

Constraints on Surrogate Keys? Empty Re: Constraints on Surrogate Keys?

Post  ngalemmo Thu Mar 21, 2013 3:20 pm

Since you go through the bother of assigning surrogate keys, there is no point in enforcing FK relationships. However, it is sometimes useful to declare them (without enforcement) to aid the optimizer.

Under NO circumstances should you delete dimension rows without a process in place to also purge facts. In general, there is no benefit of deleting dimension rows under normal circumstances. You simply will not recover significant space to make much difference.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Constraints on Surrogate Keys? Empty Re: Constraints on Surrogate Keys?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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