[lrobinovitch]

This github issue is often linked when this topic is discussed: https://github.com/github/gh-ost/issues/331

> 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.

[iLoveOncall]

> 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.

This is not a valid argument at all and I'm concerned anyone would think it is.

If you have a foreign key, it means you have a dependency that needs to be updated or deleted. If that's the case, you will have an overhead anyway, the only question being whether it's at the DB level or at the application level.

I don't think there are many cases where there's any advantage to self-manage them at the application level.

> FKs don't work well with online schema migrations

This seems to be related only to the specific project that the issue is about if you read about the detailed explanation below.

[butlerm]

> If that's the case, you will have an overhead anyway, the only question being whether it's at the DB level or at the application level.

Inserts and updates do not require referential integrity checking if you know that the reference in question is valid in advance. Common cases are references to rows you create in the same transaction or rows you know will not be deleted.

If you actually want to delete something that may be referred to elsewhere then checking is appropriate of course, and in many applications such checking is necessary in advance so you have some idea whether something can be deleted (and if not why not). That type of check may not be race free of course, hence "some idea".

[randomdata]

> the only question being whether it's at the DB level or at the application level.

It is not a binary situation like that. With the rise of 'n-tier' systems that are ever so popular today, there are often multiple DB levels. The question is not so much if it should go into the end user application – pretty much everyone will say definitely not there – but at which DB level it should it go in. That is less clear, and where you will get mixed responses.

[brlewis]

Note that this was written in 2016 in the context of a mysql-centric project. You will not find an "unchanging strong opinion that foreign keys should not be used" outside that context.

I haven't kept up with mysql enough to know if there are still good reasons to avoid foreign keys. I just stick with postgresql.

[richardwhiuk]

sharding is still a big problem for foreign keys

[shayonj]

ah! thats a blast from the past. I maintain pg-osc (online schema change tool for postgres) and very much agree that FKs make OSC hard.

[grep_it]

The responder to that issue has also written some blog posts that go into more detail on the subject.

* https://code.openark.org/blog/mysql/things-that-dont-work-we...

* https://code.openark.org/blog/mysql/the-problem-with-mysql-f...