May 8, 2009 at 8:17 am
What would be a better design. Whether a database with all it's foreign key defined or a database with some foreign key defined and the one's wherein it not defined will be taken care from the front end application.
I read somewhere that if you have already defined the foreign key then the DB engine has an extra work of checking the referenced table for the avaialability of corresponding data before inserting/deleting. This could effect the performance.
On most of the COTS products I have noticed that they only define the few major foreign key relations and integrity is maintained from the application level.
So my question is, if you agree to the practice of minimum foreign key references then how would you justify your choice of the candidate for the foreign key reference?
If you feel that defining all your foreign key is a better option then would you tell your experience with large database (maybe with over 100 tables) in terms of performance.
May 8, 2009 at 9:58 am
I am a firm believer in defining all foreign keys in the database. Yes, there is a small performance hit in verifying the key, but you have to verify the key value in the application as well. I have seen more problems come up because of bad data than because the database has foreign keys defined.
IMHO, foreign keys is what makes a relational database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 8, 2009 at 10:40 am
I agree with Jack. In most circumstances, the cost of the FK is well worth the benefits of the FK. In exceedingly rare circumstances you can have the kind of volume where that small lookup cost actually hurts performance. But in general, the work required to clean up bad data, errors in reports caused by bad data, stuff like that, far out weighs the small performance hit.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 8, 2009 at 1:04 pm
Let me start by saying that I personally love FKs.
Having said that FKs certainly add overhead to the database engine, that's a fact.
Looking at your comment below...
Sanjay (5/8/2009)...large database (maybe with over 100 tables)
... I would say that it's not how many tables you have in your database, it is the volume of transactions on your database that would make overhead count.
If you have a 1,000 tables database with 100 transactions a day you don't care about FK overhead but if you have a 100 tables database with 10,000 concurrent users in a very fast paced OLTP environment -believe me - better for you to start caring about processing overhead.
So my answer would be, it all depends.
If your environment can afford to define FKs, I'll go for it.
If because of FKs overhead your system is not going to be able to comply with SLAs then forget about them.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 11, 2009 at 7:45 am
Thanks to Jack, Grant and Paul for thier invaluable input. As I started learning Relational database, I did had all the foreign key relations defined. But over a period of time I had seen some Off-the-shelf products which mostly do not define all the foreign key relations(I assume you might also have come across some COTS product). So just wanted to get an insight from the experts.
Thanks again.
May 12, 2009 at 8:08 am
I am for FKs and all constraints necessary. No way an application should be the only one handling this kind of checking.
Other reason would be the JOIN elimmination that SQL Server does so well. With FKs and constraints the DB would NEVER be able to do such efficient query plans.
Remember that for efficient FK use you must index correctly. You can introduce a big performance hit if you do this wrong. Remember inserting into a table makes any RDBMS check up if the data is valid so it must hit the dependant table also. Without indexing this means a table scan...
May 12, 2009 at 8:24 am
Thanks Hans...
May 12, 2009 at 1:42 pm
Hans Lindgren (5/12/2009)Remember that for efficient FK use you must index correctly. You can introduce a big performance hit if you do this wrong. Remember inserting into a table makes any RDBMS check up if the data is valid so it must hit the dependant table also. Without indexing this means a table scan...
:w00t: table scan?! are you perhaps suggesting you can have a FK pointing to a flat table?
When you insert in the master table no external referential integrity check is needed.
When you insert in the variable table you check referential integrity once per FK seatting in your variable table but... FKs must point either to a PK or a Unique Index!!!
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 13, 2009 at 10:28 am
What he is saying is that if you have Table 1 ( PK int ) and Table 2 ( FK int )
Lets say for instance you want to delete a row from Table 1 and Table 2 has millions of records. If you do not have proper indexing then a Table Scan will occur on Table 2 to see if the Foreign Key exists in order to allow the delete or to error with a foreign key onstraint error.
With proper indexing the constraint lookup is fast and minimal hit to performance.
May 13, 2009 at 10:45 am
Joel Ewald (5/13/2009)
What he is saying is that if you have Table 1 ( PK int ) and Table 2 ( FK int )Lets say for instance you want to delete a row from Table 1 and Table 2 has millions of records.
Poster is not saying that... let me quote what poster says "...inserting into a table makes any RDBMS check up if the data is valid."
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 14, 2009 at 5:51 am
Sabu Thomas
System Auditor
As I heard from the documentation that, when we creating a FK, create an index automaticaly, no need for an additional index key for the FK
May 14, 2009 at 6:23 am
No index is automatically created when you create foreign keys.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 14, 2009 at 6:30 am
sabutp (5/14/2009)As I heard from the documentation that, when we creating a FK, create an index automaticaly, no need for an additional index key for the FK
This is a urban mith -you may want to test it.
SQL Server does not makes mandatory to have an index on a FK but suggests FKs make good "index candidates".
In my book you do not create an indexless FK; the same way you don't want to shoot yourself in the feet you don't have an indexless FK.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 14, 2009 at 6:30 am
Sabu Thomas
System Auditor
I am not sure in MS SQL but in Sysbase SQL separate indexes are created automatically for primary and foreign keys.
May 14, 2009 at 6:46 am
sabutp (5/14/2009)I am not sure in MS SQL but in Sysbase SQL separate indexes are created automatically for primary and foreign keys.
MS SQL Server != Sybase
They departed ways when SQL Server released version 4.2
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply