5

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?

Improve this question
1
  • 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
    Commented Mar 12, 2012 at 15:10

1 Answer 1

Reset to default
19

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?

Improve this answer
6
  • Thanks, Shark - that was what I suspected, but wanted a little validation. Isn't that what we all want, after all? :)
    – JHFB
    Commented Mar 12, 2012 at 15:15
  • @JHFB :) Agreed.
    – Thomas Stringer
    Commented Mar 12, 2012 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
    Commented Mar 12, 2012 at 18:19
  • @GrantFritchey I did not know that, and a very interesting article indeed. Thank you for the lesson!
    – Thomas Stringer
    Commented Mar 12, 2012 at 18:43
  • 2
    Way too much credit to me on that, but this makes the answer much better, yes.
    – Grant Fritchey
    Commented Mar 12, 2012 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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