2

Object Relational Mapping (take ActiveRecord and Rails, for example) allows a programmer to define relations between models, and callbacks on delete.

So my question is: do I still need to define foreign keys in my database? does a foreign key have other functions (speed for example?), or its only function is to preserve relational integrity?

2 Answers 2

6

Foreign keys can indeed speed up queries if they are trusted. If the engine knows that every value in ParentID in the Child table refers to a valid PK in Parents then it won't even look at that table to check.

However, relational constraints are a good idea in general regardless. One philosophy behind constraints in the database is to use them as a last-resort stop for bad data. Foreign keys are a good example of this.

If you depend entirely on your ORM for relation management, what happens if you change to a different framework? You still need to keep your existing data. What happens if for some reason you need to make changes to your relational model in the ORM and orphaned child records get put in with no parent?

Short answer - yes, you should still use keys and constraints in the database.

4
  • So a child would ask: why ruby on rails doesn't have the possibility to create a FK? If they are not stupid, and if FKs are really useful, (I really believe they are not) they should have included it Jun 27, 2014 at 13:26
  • 1
    I honestly can't say why they didn't include it. It doesn't imply stupidity but it may imply ignorance.
    – JNK
    Jun 27, 2014 at 13:43
  • 3
    Another reason: I have never seen a database with vital (enterprise) information that was only accessed by a single application. So it is unlikely that the application (and thus that specific ORM) will be the only thing that writes to the database.
    – user1822
    Jun 27, 2014 at 14:04
  • Okay, you convinced me. Jun 27, 2014 at 15:26
2

A Foreign Key constraint is primarily there to preserve integrity, though there are some performance benefits.

The good thing about foreign key constraints is that they are always in force in the database, instead of depending on the ORM to enforce it. The software platform for your application can change, gaining or losing features, but if the database is enforcing integrity then it safeguards you from platform changes and programming errors.

So, yes, use the foreign key constraints.

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.