Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a replicated copy of an application database. I am exploring how to change the constraints and indexes to better serve our reporting requirements. Because this is a copy of the application database, I know that the referential integrity is in check.

What is the impact of primary key and foreign key relationships in this copy? If I carefully set up the indexing I require, is there any benefit to foreign keys? Any detriment?

share|improve this question
2  
They serve different purposes. You can't have a foreign key check in an index and a FK constraint won't (always) speed up a query –  JNK Mar 12 '12 at 15:10
add comment

1 Answer 1

up vote 18 down vote accepted

Foreign Key Constraints and Indexes are two completely separate entities within a database. As you have explicitly stated in your question, foreign key relationships are to ensure Referential Integrity. If your database copy is soley for OLAP that wouldn't really come into play (as long as you aren't doing any DML batches against it).

The indexes are what are going to impact your query performance. And if the above is true (no INSERT, UPDATE, or DELETE), you shouldn't be too concerned with the overhead of index maintenance.

Because of this, you can wisely choose indexes in order to maximize performance. But without seeing more details on your data, and what already exists, generalized ideas are all that can be given to you.

EDIT: As per Grant Fritchey's blog post, there can be a performance gain due to foreign key constraints, as the query optimizer can omit certain unnecessary operations due to the referential aspects of the constraint. For more information, please see Grant's blog post: Do Foreign Key Constraints Help Performance?

share|improve this answer
    
Thanks, Shark - that was what I suspected, but wanted a little validation. Isn't that what we all want, after all? :) –  JHFB Mar 12 '12 at 15:15
    
@JHFB :) Agreed. –  Thomas Stringer Mar 12 '12 at 15:19
4  
Slight disagreement. Foreign key constraints can and do affect performance and execution plans. Here's a blog post about it: scarydba.com/2010/11/22/… . No, they don't take the place of indexes. They're used in conjunction with indexes. –  Grant Fritchey Mar 12 '12 at 18:19
    
@GrantFritchey I did not know that, and a very interesting article indeed. Thank you for the lesson! –  Thomas Stringer Mar 12 '12 at 18:43
2  
Way too much credit to me on that, but this makes the answer much better, yes. –  Grant Fritchey Mar 12 '12 at 19:37
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.