At GitHub we do not use foreign keys, ever, anywhere.
Personally, it took me quite a few years to make up my mind about whether foreign keys are good or evil, and for the past 3 years I’m in the unchanging strong opinion that foreign keys should not be used. Main reasons are:
FKs are in your way to shard your database. Your app is accustomed to rely on FK to maintain integrity, instead of doing it on its own. It may even rely on FK to cascade deletes (shudder).
When eventually you want to shard or extract data out, you need to change & test the app to an unknown extent.
FKs are a performance impact. The fact they require indexes is likely fine, since those indexes are needed anyhow. But the lookup made for each insert/delete is an overhead.
FKs don’t work well with online schema migrations.
This last bullet is not a chicken and an egg, as you might think. FKs impose a lot of constraints on what’s possible and what’s not possible.
Here’s an old post of mine, reviewing the first appearance of Facebook’s OSC, and which includes some thoughts on foreign keys: http://code.openark.org/blog/mysql/mk-schema-change-check-out-ideas-from-oak-online-alter-table
Let’s say you have two tables, P & C, standing for Parent & Child, respectively. There’s a foreign key in C such that each row in C points to some “parent” value in P.
Doing schema migration of C is possible. However since foreign keys have unique names, the new (migrated) C table will have a FK with a different name than the original one.
Doing schema migration of P is just not going to work. Recall that gh-ost renames the table at the end. Alas, when renaming a table away, the FK will move with the renamed table. To create a parent-side FK on the ghost table, one would need to migrate C ; and because gh-ost uses async approach, P and P-ghost are never in complete sync at any point in time (except at lock time) which makes it impossible for C to have both a FK to P and to P-ghost. some integrity will be broken.
There’s more discussion on the documentation of pt-online-schema-change
