Are foreign keys really necessary in a database design?
Foreign keys help enforce referential integrity at the data level. They also improve performance because they're normally indexed by default.
Foreign keys can also help the programmer write less code using things like ON DELETE CASCADE. This means that if you have one table containing users and another containing orders or something, then deleting a user could automatically delete all orders that point to that user.
I can't imagine designing a database without foreign keys. Without them, eventually you are bound to make a mistake and corrupt the integrity of your data.
They are not required, strictly speaking, but the benefits are huge.
I'm fairly certain that FogBugz does not have foreign key constraints in the database. I would be interested to hear how the Fog Creek Software team structures their code to guarantee that they will never introduce an inconsistency.
A database schema without FK constraints is like driving without a seat belt.
One day, you'll regret it. Not spending that little extra time on the design fundamentals and data integrity is a sure fire way of assuring headaches later.
Would you accept code in your application that was that sloppy? That directly accessed the member objects and modified the data structures directly.
Why do you think this has been made hard and even unacceptable within modern languages?
Yes.
- They keep you honest
- They keep new developers honest
- You can do
ON DELETE CASCADE - They help you to generate nice diagrams that self explain the links between tables