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 have heard this argument too - from people who forgot to put an index on their foreign keys and then complained that certain operations were slow (because constraint checking could take advantage of any index). So to sum up: There is no good reason not to use foreign keys. All modern databases support cascaded deletes, so...

Answer 2

Additional Reason to use Foreign Keys: - Allows greater reuse of a database

Additional Reason to NOT use Foreign Keys: - You are trying to lock-in a customer into your tool by reducing reuse.

Answer 3

To quote Joe Celko:

"like a size 26 thong, just because you can does not mean you should!"

I'm sure there are plenty of applications where you can get away with it, but it's not the best idea. You can't always count on your application to properly manage your database, and frankly managing the database should not be of very much concern to your application.

If you are using a relational database then it seems you ought to have some relationships defined in it. Unfortunately this attitude (you don't need foreign keys) seems to be embraced by a lot of application developers who would rather not be bothered with silly things like data integrity (but need to because their companies don't have dedicated database developers). Usually in databases put together by these types you are lucky just to have primary keys ;)

Answer 4

Reasons to use Foreign Keys:

• you won't get Orphaned Rows
• you can get nice "on delete cascade" behavior, automatically cleaning up tables
• knowing about the relationships between tables in the database helps the Optimizer plan your queries for most efficient execution, since it is able to get better estimates on join cardinality.
• FKs give a pretty big hint on what statistics are most important to collect on the database, which in turn leads to better performance
• they enable all kinds of auto-generated support -- ORMs can generate themselves, visualization tools will be able to create nice schema layouts for you, etc.
• someone new to the project will get into the flow of things faster since otherwise implicit relationships are explicitly documented

Reasons not to use Foreign Keys:

• you are making the DB work extra on every CRUD operation because it has to check FK consistency. This can be a big cost if you have a lot of churn
• by enforcing relationships, FKs specify an order in which you have to add/delete things, which can lead to refusal by the DB to do what you want. (Granted, in such cases, what you are trying to do is create an Orphaned Row, and that's not usually a good thing). This is especially painful when you are doing large batch updates, and you load up one table before another, with the second table creating consistent state (but should you be doing that sort of thing if there is a possibility that the second load fails and your database is now inconsistent?).
• sometimes you know beforehand your data is going to be dirty, you accept that, and you want the DB to accept it
• you are just being lazy :-)

I think (I am not certain!) that most established databases provide a way to specify a foreign key that is not enforced, and is simply a bit of metadata. Since non-enforcement wipes out every reason not to use FKs, you should probably go that route if any of the reasons in the second section apply.

Answer 5

"They can make deleting records more cumbersome - you can't delete the "master" record where there are records in other tables where foreign keys would violate that constraint."

It's important to remember that the SQL standard defines actions that are taken when a foreign key is deleted or updated. The ones I know of are:

• ON DELETE RESTRICT - Prevents any rows in the other table that have keys in this column from being deleted. This is what Ken Ray described above.
• ON DELETE CASCADE - If a row in the other table is deleted, delete any rows in this table that reference it.
• ON DELETE SET DEFAULT - If a row in the other table is deleted, set any foreign keys referencing it to the column's default.
• ON DELETE SET NULL - If a row in the other table is deleted, set any foreign keys referencing it in this table to null.
• ON DELETE NO ACTION - This foreign key only marks that it is a foreign key; namely for use in OR mappers.

These same actions also apply to ON UPDATE.

The default seems to depend on which sql server you're using.

Answer 6

I echo the answer by Dmitriy - very well put.

For those who are worried about the performance overhead FK's often bring, there's a way (in Oracle) you can get the query optimiser advantage of the FK constraint without the cost overhead of constraint validation during insert, delete or update. That is to create the FK constraint with the attributes RELY DISABLE NOVALIDATE. This means the query optimiser ASSUMES that the constraint has been enforced when building queries, without the database actually enforcing the constraint. You have to be very careful here to take the responsibility when you populate a table with an FK constraint like this to make absolutely sure you don't have data in your FK column(s) that violate the constraint, as if you do so you could get unreliable results from queries that involve the table this FK constraint is on.

I usually use this strategy on some tables in my data mart schema, but not in my integrated staging schema. I make sure the tables I am copying data from already have the same constraint enforced, or the ETL routine enforces the constraint.