Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
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 –  Vecchia Spugna Jun 27 at 13:26
    
I honestly can't say why they didn't include it. It doesn't imply stupidity but it may imply ignorance. –  JNK Jun 27 at 13:43
2  
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. –  a_horse_with_no_name Jun 27 at 14:04
    
Okay, you convinced me. –  Vecchia Spugna Jun 27 at 15:26

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.