What's wrong with foreign keys?

Question

I remember hearing Joel Spolsky mention in podcast 014 that he'd barely ever used a foreign key (if I remember correctly). However, to me they seem pretty vital to avoid duplication and subsequent data integrity problems throughout your database.

Do people have some solid reasons as to why (to avoid a discussion in lines with Stack Overflow principles)?

Edit: "I've yet to have a reason to create a foreign key, so this might be my first reason to actually set up one."

Answer 1

I always thought it was lazy not to use them. I was taught it should always be done. But then, I didnt listen to Joel's discussion. He may have had a good reason, I don't know.

Answer 2

There's one good reason not to use them: If you don't understand their role or how to use them.

In the wrong situations, foreign key constraints can lead to waterfall replication of accidents. If somebody removes the wrong record, undoing it can become a mammoth task.

Also, conversely, when you need to remove something, if poorly designed, constraints can cause all sorts of locks that prevent you.

Answer 3

The Clarify database is an example of a commercial database that has no primary or foreign keys.

http://www.geekinterview.com/question_details/18869

The funny thing is, the technical documentation goes to great lengths to explain how tables are related, what columns to use to join them etc.

In other words, they could have joined the tables with explicit declarations (DRI) but they chose not to.

Consequently, the Clarify database is full of inconsistencies and it underperforms.

But I suppose it made the developers job easier, not having to write code to deal with referential integrity such as checking for related rows before deleting, adding.

And that, I think, is the main benefit of not having foreign key constraints in a relational database. It makes it easier to develop, at least that is from a devil-may-care point of view.

Answer 4

@imphasing - this is exactly the kind of mindset that causes maintenance nightmares.

Why oh why would you ignore declarative referential integrity, where the data can be guaranteed to be at least consistent, in favour of so called "software enforcement" which is a weak preventative measure at best.

Answer 5

Verifying foreign key constraints takes some CPU time, so some folks omit foreign keys to get some extra performance.

Answer 6

To me, if you want to go by the ACID standards, it is critical to have foreign keys to ensure referential integrity.