Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
HSA
Starting Member
1 Post |
 Posted - 2009-01-16 : 14:13:45
|
| Hi:Our DBA keeps insisting that we should avoid defining too many FK constraints on a table. He prefers that if we have several "logical FKs" in a table, we should define the FK constraint only for the ones that have a high chance of becoming inconsistent.His reasoning is that the DB has to do some checks to enforce those FKs and those checks increase locks and contention and reduce teh performance.However, I think that the DB does those checks only when you remove a row from the primary table (or update its ID) or update the FK column. If you only update non-key rows and never delete rows from the primary table (a situation very common with enterprise applications), then there is no need fo rthe DB to check anything to enforce FKs. Because of this, I believe there is no point in being stingy on adding FK constraints when you have a logical FK relationship. They guard you against mistakenly deleting referenced rows manually (or due to a bug) while adding almost zero overhead for usual operations.However, he is the DBA and has 20 years of experience and I'm only a Java developer, so it's hard to convince management that I'm right.I'd like to know what you guys think. Does my argument make any sense? Is there any book or article that suggests there are cases that you should avoid defining FK constraints?BTW the issue of defining indexes for the FK is orthogonal to my argument. I know that you shouldn't just blindly define indexes for every FK because they slow down updates. My point is that a FK constraint "by itself" doesn't add any overhead for usual non-key UPDATE operations (regardless of it being indexed).Thanks |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-01-16 : 14:56:31
|
| If you are looking to define a bunch of tables with more than 5 or 6 foreign keys each, your DBA may have more concerns about the overall database design. There is some overhead for the lookup, if you update/insert child records. The system will necessarily need to find the parent record to make sure that it in fact exists. Normally, this lookup rides right down the primary key index, so should not be a large impact, unless you are doing literally thousands of inserts per second.You may want to create a test setup. Create a set of parent-child tables, run a few thousand inserts/updates, time it, and have the DBA check the output in Profiler. Set up the foreign keys, then run the same test. My money will be on the second test being a bit longer, but probably not significantly longer.Oh, and for political reasons, don't go to management to do this test. Try to keep it between you and the DBA. Think of the systems of 15 - 20 years ago when this DBA was starting out. 20 years ago, the 486 was king. 15 years ago, the Pentium was almost out. Memory was typically measured in increments of 8MB. Hardware has come a long way since he probably formed his opinions. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-16 : 15:07:27
|
| Agree with mcrowley reasoning:Also look at this:http://clay.lenharts.net/blog/2007/11/17/are-foreign-keys-bad/ |
|
|
|
|
|
|
|
|
Do FK constraints degrade performance?