November 14, 2007 at 6:54 pm
As someone else has pointed out, the only time I've seen foreign keys not be the best choice is in extreme cases due to performance. Otherwise, you want this additional check to keep data integrity.
Is there a performance hit? Yes. Consider what the foreign key check has to do. It has to look up in the parent table to ensure that a key exists before inserting a row. Or it has to check in the child tables to see rows exist before updating or deleting a row. That's why there's a recommendation to put indexes on the columns that comprise a foreign key.
Unless you have an extreme performance case, though, it is unlikely you'll notice the performance hit. And as someone else already stated, you're going to have to do the lookup either at the database level or at the application level. The application level tends to increase latency and locking because now you have to include network access time as well as processing time in the application.
K. Brian Kelley
@kbriankelley
November 14, 2007 at 7:07 pm
one thing i can think they are good for is maybe reducing joins. we have queries where there is more joins than you can count and there is a performance hit. if we had more FK's i think the queries could be rewritten to have less joins and perform better
November 14, 2007 at 7:23 pm
SQL Noob (11/14/2007)
one thing i can think they are good for is maybe reducing joins. we have queries where there is more joins than you can count and there is a performance hit. if we had more FK's i think the queries could be rewritten to have less joins and perform better
Foreign keys are a constraint. Why would you use them to reduce joins?
K. Brian Kelley
@kbriankelley
November 14, 2007 at 9:53 pm
Thanks Guys !!!! The Posts were really informative as i got to know :
That Foreign key's do effect performace when it comes to inserts / updates / delete's but they are essential for data integrity ......
And also indexing should be done on primary key and foreign key as foreign key will be used to join tables ......................
Thanks a ton :):)
November 14, 2007 at 10:12 pm
SQL Noob (11/14/2007)
there are no good and bad or black or white in SQL, only gray areas and what is good for your situationwe have very few FK's where i work as well because the devs like the application this way. a lot of our business is sales people making up manual orders and they rarely get it exactly right. and the price is that we have some people spend a fair amount of time fixing data mistakes in the database that could have been prevented with constraints. but then again we would probably have the same thing since no one would get it right and put in trouble tickets that their data input is not working
I'll have to disagree here. I think that database design is black and white. There is an correct way to design proper, normalized relational data structures. You may at some point make exceptions with normalizations or FK constraints for performance reasons, but they should be just that, exceptions and not grey areas. I'm not sure of your job position, but if you are a database designer, architect, or administrator, question everything that your developers do 'because they like it that way'. If you are going to use a database to house unrelated, unnormalized data, why not just use spreadsheets on fast disks?
November 15, 2007 at 12:25 am
I am a developer who has been given task to develop upon a database with no foreign keys.
currently data will be passed to me from the data layer and i just need to insert it into the databse (not even think about creation of data at the database level)
But to be on the safer side i think i will go with the foreign key constraints so that atleast data which is populated can be thought as "Clean data" ....
November 15, 2007 at 3:08 am
There are plenty of articles out on the Internet about why foreign key contraints are good to ensure data integrity. Perhaps this is an opportunity for you to be able to educate your management and peers as to why this is such a big deal.
K. Brian Kelley
@kbriankelley
November 15, 2007 at 4:14 am
Yes I do understand the importance of the foreign keys for maintaining the integrity ................but what the question is that if the system is working without it why do we need to add further more complexity to it ? Cause the system is already in place and is fully functional
Why do i want to complicate things which are simple?
I understand it is important but i do not have enough justifications to go forward and add the constraints ...
November 15, 2007 at 5:40 am
Megha Yadav (11/15/2007)
Yes I do understand the importance of the foreign keys for maintaining the integrity ................but what the question is that if the system is working without it why do we need to add further more complexity to it ? Cause the system is already in place and is fully functionalWhy do i want to complicate things which are simple?
I understand it is important but i do not have enough justifications to go forward and add the constraints ...
I used to work in consulting as a permanent employee for a consulting firm. My boss had a saying, "You can listen to me and pay me once, or you can not listen to me and keep on paying me for a long time." You're either doing something to protect your data or you're not. The justification for "complicating" the system is that you're implementing standard, best practices, to ensure data integrity over the long haul. They'll listen to you, you'll have a short term hit on some development time while you do the implementation and everything will be fine. On the other hand, they won't listen to you, you'll deliver an application, maybe 3-4 days faster than you would have otherwise, and then at some point down the road, they'll wonder why they can't get consistent data out of the database for a financial report and tons of time and money will be spent attempting to clean up the data. Pay once or keep on paying. Their call.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 15, 2007 at 6:14 am
Megha Yadav (11/15/2007)
Yes I do understand the importance of the foreign keys for maintaining the integrity ................but what the question is that if the system is working without it why do we need to add further more complexity to it ? Cause the system is already in place and is fully functionalWhy do i want to complicate things which are simple?
I understand it is important but i do not have enough justifications to go forward and add the constraints ...
if you are close to outgrowing your hardware and no one wants to buy new hardware than FK's can improve performance. you probably have queries that join two columns in two tables for referential integrity in the application. we have queries with 20 joins in a lot of tables that spike the CPU and use a lot of resources. having FK's you can rewrite these without the joins since you know the FK exists in the table with the PK
November 15, 2007 at 6:16 am
K. Brian Kelley (11/14/2007)
SQL Noob (11/14/2007)
one thing i can think they are good for is maybe reducing joins. we have queries where there is more joins than you can count and there is a performance hit. if we had more FK's i think the queries could be rewritten to have less joins and perform betterForeign keys are a constraint. Why would you use them to reduce joins?
becasuse we have queries with a ton of joins whose sole purpose is to verify that data in a table with the FK exists in another table. with PK/FK relationships we can rewrite them to be a lot faster and a lot less joins if any
November 15, 2007 at 6:23 am
John Rowan (11/14/2007)
SQL Noob (11/14/2007)
there are no good and bad or black or white in SQL, only gray areas and what is good for your situationwe have very few FK's where i work as well because the devs like the application this way. a lot of our business is sales people making up manual orders and they rarely get it exactly right. and the price is that we have some people spend a fair amount of time fixing data mistakes in the database that could have been prevented with constraints. but then again we would probably have the same thing since no one would get it right and put in trouble tickets that their data input is not working
I'll have to disagree here. I think that database design is black and white. There is an correct way to design proper, normalized relational data structures. You may at some point make exceptions with normalizations or FK constraints for performance reasons, but they should be just that, exceptions and not grey areas. I'm not sure of your job position, but if you are a database designer, architect, or administrator, question everything that your developers do 'because they like it that way'. If you are going to use a database to house unrelated, unnormalized data, why not just use spreadsheets on fast disks?
less constraints is a lot more flexible
we bought out some competitors and it's easier to migrate data to our systems because we don't have to worry about constraints. it's alot a lot easier to get people to use applications on our systems without constraints and migrate the rest of the data later
the original design philosophy 10 years ago was portability and to have referential integrity in the java code as part of the application because the old business model was to sell this app to others as a side business and to have it run on a variety of database servers with minimal configuration and problems
a lot of our data is also varchar and we have a ton of UDT's for business rules and to control the data people input. a lot o our FK's would be varchars as well and i think it would cause just as much problems because people wouldn't input the right data and would always get a ton of errors
November 15, 2007 at 7:42 am
Megha Yadav (11/15/2007)
Yes I do understand the importance of the foreign keys for maintaining the integrity ................but what the question is that if the system is working without it why do we need to add further more complexity to it ? Cause the system is already in place and is fully functionalWhy do i want to complicate things which are simple?
I understand it is important but i do not have enough justifications to go forward and add the constraints ...
Because without constraints, your one application update from data issues. Foreign key constraints are intended as a check on the database level to ensure relationships are enforced. Think of it in terms of a contract.
K. Brian Kelley
@kbriankelley
November 15, 2007 at 7:53 am
K. Brian Kelley (11/15/2007)
Megha Yadav (11/15/2007)
Yes I do understand the importance of the foreign keys for maintaining the integrity ................but what the question is that if the system is working without it why do we need to add further more complexity to it ? Cause the system is already in place and is fully functionalWhy do i want to complicate things which are simple?
I understand it is important but i do not have enough justifications to go forward and add the constraints ...
Because without constraints, your one application update from data issues. Foreign key constraints are intended as a check on the database level to ensure relationships are enforced. Think of it in terms of a contract.
To go one step further - how do you KNOW it's "working"? Do you have a mechanism to prove to yourself that you don't have orphans/widows? You are sure and are able to prove that when a key updates in one table, the rows in another that were related to that key change WITH it?
That's the value added by the foreign key constraints.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 15, 2007 at 8:04 am
SQL Noob (11/15/2007)
less constraints is a lot more flexiblewe bought out some competitors and it's easier to migrate data to our systems because we don't have to worry about constraints. it's alot a lot easier to get people to use applications on our systems without constraints and migrate the rest of the data later
the original design philosophy 10 years ago was portability and to have referential integrity in the java code as part of the application because the old business model was to sell this app to others as a side business and to have it run on a variety of database servers with minimal configuration and problems
a lot of our data is also varchar and we have a ton of UDT's for business rules and to control the data people input. a lot o our FK's would be varchars as well and i think it would cause just as much problems because people wouldn't input the right data and would always get a ton of errors
If your application logic is already checking the data, then the foreign keys wouldn't present an additional problem in that a bunch of errors getting thrown. If your application logic isn't checking the data, all the more reason to do the foreign key checks.
And while I agree with you that they may have been an older model, that doesn't make it the right one. MySQL didn't originally support foreign keys, but some of the database engines do now as it continues to expand its role in the enterprise. Therefore, all the major database platforms in the enterprise support foreign keys. That, in and of itself, is a reason to consider it.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 16 through 30 (of 87 total)
You must be logged in to reply to this topic. Login to reply