Are foreign keys really necessary in a database design?

Question

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.

1. They keep you honest
2. They keep new developers honest
3. You can do ON DELETE CASCADE
4. They help you to generate nice diagrams that self explain the links between tables

Suppose a programmer is actually doing this in the right manner already

Making such a supposition seems to me to be an extremely bad idea; in general software is phenomenally buggy.

And that's the point, really. Developers can't get things right, so ensuring the database can't be filled with bad data is a Good Thing.

Although in an ideal world, natural joins would use relationships (i.e. FK constraints) rather than matching column names. This would make FKs even more useful.

Personally, I am in favor of foreign keys because it formalizes the relationship between the tables. I realize that your question presupposes that the programmer is not introducing data that would violate referential integrity, but I have seen way too many instances where data referential integrity is violated, despite best intentions!

Pre-foreign key constraints (aka declarative referential integrity or DRI) lots of time was spent implementing these relationships using triggers. The fact that we can formalize the relationship by a declarative constraint is very powerful.

@John - Other databases may automatically create indexes for foreign keys, but SQL Server does not. In SQL Server, foreign key relationships are only constraints. You must defined your index on foreign keys separately (which can be of benefit.)

Edit: I'd like to add that, IMO, the use of foreign keys in support of ON DELETE or ON UPDATE CASCADE is not necessarily a good thing. In practice, I have found that cascade on delete should be carefully considered based on the relationship of the data -- e.g. do you have a natural parent-child where this may be OK or is the related table a set of lookup values. Using cascaded updates implies you are allowing the primary key of one table to be modified. In that case, I have a general philosophical disagreement in that the primary key of a table should not change. Keys should be inherently constant.