Poll: Foreign Key Constraints

Do you create foreign key constraints between dimensions and facts in your relational star schemas?

I don't want to bias the results in any way, so I won't post my opinion just yet. But a recent discussion got me thinking about the following question and I'm interested to hear what other peoples approaches are.

Follow this link to get to the online poll

Feel free to post comments if you want to explain the reasons for your answer.

Print | posted on Wednesday, March 24, 2010 4:05 PM

Comments on this post

# re: Poll: Foreign Key Constraints

Requesting Gravatar...
Given that our preference s/b for more documentation and not less, I always implement FKs. *However*, if push comes to shove, we can always disable them...yet they still appear in Database Diagrams! (The real issue revolves around indexes, in any case.) Readers may appreciate the following as a starting point for their own tests:

USE DocumentFKs;
<create tables w/ FKs>
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
GO
<add child rows that violate FK, look at Database Diagrams, etc. Note how diagram lines have dashes>
<add parent PK rows that satisfy FKs>
EXEC sp_MSforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL';
GO
<check Database Diagrammer. Pay attn to lines>
Left by SteveOLAP on Mar 26, 2010 8:14 AM

# re: Poll: Foreign Key Constraints

Requesting Gravatar...
Any the results of the poll?
Left by Mitch Wheat on Apr 30, 2010 12:22 PM

# re: Poll: Foreign Key Constraints

Requesting Gravatar...
@Mitch - Thanks for the prompt. There is a link to the raw results on poll itself. I am planning to do a post about this, but probably will not get time to post it until the weekend.
Left by Darren Gosbell on May 04, 2010 9:15 AM

Your comment:

 (will show your gravatar)