Real Internet Applications Don't Use Foreign Keys
Tagged:  •    •  

Just a disclaimer: I've never done a "real" web app. My opinion in the following subject is extremely fragile and can be easily refuted.

A long long time ago, my father, while I was studying for the Database Systems course, asked me (ironically) if I could explain him what's the use for Foreign Keys on today's databases.

And the truth, after all these years, I still haven't been able to give him an explanation.

Fourth generation programming languages (I'm talking about SQL) were designed to be usable by non-programmer in a simple way. It was supposed for "clients" of a database to interact directly with the data through a very high level language.

Fortunately this has never happened, and the communication with databases is always wrapped by some kind of code (sometimes with an ORM framework).

Foreign keys are constraints which serve to guarantee data integrity. They are useful in the hypothetical situation where clients could tamper with the data directly. And, as nothing comes free, ensuring this constraints costs important CPU cycles.

Today's applications don't rely on these mechanisms to guarantee database integrity. It is the wrapper around the DB that ensures it.

So the question is: in applications where it is important to optimize database queries why use foreign keys?? And this leads to another question, why don't companies supply DBMS that don't have the foreign keys functionalities??

Can anyone answer this?

ps: In my applications I don't explicitly set foreign keys. Even if the performance is not a requirement. It just seems stupid to use something that shouldn't be there.

ps2: To make my opinion even clearer: I don't believe foreign keys in today's Relational DBMS have any relevance. They are just a performance penalty.

You believe that foreign

You believe that foreign keys in RDBMS hold no relevance? I suggest you rethink your career choice. Might I suggest basket-weaving?

And the reason is.....?

>> You believe that foreign keys in RDBMS hold no relevance?
Yes, I couldn't have put it better. You got it.

>> I suggest you rethink your career choice. Might I suggest basket-weaving?
I have done the disclaimer. And I have no problem with basket weaving. If it is better suited to me, no prob. And thanks for the tip on career change.

But can you tell me where do you think my logic fails?? It would be useful to know the reason why foreign keys are relevant in today's applications.

"So the question is, in

"So the question is, in applications where it is important to optimize database queries why use foreign keys?? And this leads to another question, why don't companies supply DBMS that don't have the foreign keys functionalities??"

Make a simple cost & return study.
We are talking about applications which tend to be updated / developed at a very fast pace.

If you are in a project alone and you make those decisions, I believe its ok (your code so your choice), but when we talk in companies:
--> where the resources are constantly changing and the need to understand the code and track errors need to be done in a very standard / easy to follow scheme;
--> and also acknowledge that adding that complexity will maybe add an exponential complexity to other programmers which are completely used to see / use foreign keys;

Remember you are introducing a complexity which can be deadly.

"ps: In my applications I don't explicitly set foreign keys. Even if the performance is not a requirement. It just seems stupid to use something that shouldn't be there."

Again your code so your rules, yet I can imagine you tracking your error log file always with a DB schema paper next to your computer. Now imagine you had to work in a different project with a different DB schema each day.

PS: basket-weaving rocks

Don't agree

>> If you are in a project alone and you make those decisions, I believe its ok (your code so your choice), but when we talk in companies:
>> --> where the resources are constantly changing and the need to understand the code and track errors need to be done in a very standard / easy to follow scheme;
>> --> and also acknowledge that adding that complexity will maybe add an exponential complexity to other programmers which are completely used to see / use foreign keys;

That's a false question. I completely agree that the cost of maintaining code is extremely high and that every possible option to don't increase the complexity of an application must be always privileged. I'm even more radical: Even if the code is your's solely, simpler is always better.

Where I don't agree is in the point of Foreign Keys being good safety nets to developer errors.

  1. Schemes are as easily to understand either mark a field as foreign or not. Mark that I do not have anything against the concept of foreign keys. The concept of foreign keys are necessary to relational databases to establish relations between different rows. What I don't agree is the DBMS to guarantee the Foreign keys restrictions. Simply leave that work to an upper layer. So your logical schema will remain equal. You simply don't mark the column as foreign key when creating the table.
  2. Programmers when maintaining/extending the application can change the DB schema at will. They can change the foreign keys. This doesn't change anything.
  3. If you're relying on the RDBMS to launch errors when data is inserted to discover development errors, something is wrong in you project management. You shouldn't rely on this phase of the project to find these errors. For example: Unit tests are exactly to discover these types of errors.

I'm extremely happy for your comments Stott. :D

Now refute mine ;)

I agree with you completely

It seems like the restrictions using foreign keys are used solely as a crutch for sloppy coding and partial understanding of the data that is being worked with. If the code is well written and tested you would know a duplicate item is abaout to be written to the DB because it would be coded for. Theres an additional hassle factor with foreign keys for those that understand the data.

Thanks for sharing your opinion.

Really appreciated your comment.

Totally agree

We have noticed a lot of performance issue because of the double validation happens on the database model.

In our real-time enterprise experience, the new systems are Rapid Application Development that takes care of almost everything related to data, and we have to have it on the application layer because we need the application to know where was the error, there is no need to pass the request to the database and then teach the application the error messages and how to rephrase the in an i18n and human readable way.

However, from its name, the database is a database and it is better to be used for what it was created for, you can read about the performance deference of running performance tests over native applications and database level Functions/Procedures.

Finally, nobody says the foreign keys are evil, but if you are writing the business logic on the front end application, just don't overhead the system by the foreign keys especially for the enterprise systems.

Best regards,
Mohamad Sibai

Thanks for sharing your experience

Thanks for bringing some real enterprise experience to the discussion.

When I wrote this blog post I was expecting only to be flamed. Couldn't be more wrong.

The comments have been extremely interesting and I thank all that took the time to share their opinion.

Introducing myself

Hi! I have been studying this site for a bit. Wanted to make a post.

Groups may be linked to

Groups may be linked to artists and countries. Any user may start a group and add members. Most groups are open to all, but membership may be subject to approval by the Group Leader. Last.fm will watch bands generate a group profile similar to the users' profiles, showing an amalgamated set of data and charting the group's overall tastes. Individual groups have their own discussion forums and journal space, and a group radio station based on members' music profiles is automatically generated once a sufficient number of members michele watches have joined. Group members are also able to submit recommendations of artists or tracks to all the other members of their group.

The whole point in foreign

The whole point in foreign keys is to make it easier and simplier to use!

I don't agree with what you

I don't agree with what you says that "I don't believe foreign keys in today's Relational DBMS have any relevance". You have to do studies or research about it. Then only you come to know that it has a big relevance in RDBMS. Because of using foreign keys things are getting easy to understand.squirrel repellent

I agree with the above

I agree with the above commentator Foreign keys play very important role in today's RDBMS. Many databases use it for creating proper relationship between two columns. you should read books that will be related to it.
used natural gas generators

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>. Beside the tag style "<foo>" it is also possible to use "[foo]".

More information about formatting options

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions. It will also improve your math skills :P