"At GitHub we do not use foreign keys, ever, anywhere" (2016) | Hacker News Reader

"At GitHub we do not use foreign keys, ever, anywhere" (2016)

github.com

17 pointsshubhamjain14 comments

Good example of programmers rationalizing about not understanding the relational model and ACID properties. Databases have to ensure and enforce referential integrity and prevent update anomalies. When you have the choice to do that in the database or in application code, choosing the database almost always gives more reliable and future-proof results.

Dismissing an important feature -- foreign keys -- because of a migration tool communicates some ignorance of the relational model, ACID, and a bias to application code generating the database schema, rather than the database shaping the application code. Learn SQL.

Hell, why do you eve need FKs in a non financial/non mission critical environment? The worst that can happen in a sanely created environment is that some data is left dangling in some table.

I consider GitHub mission critical, I think a lot of developers do.

"Some data left dangling in some table" is the least of the problems. Selecting from those tables will fail to join the parent, or get nulls for the parent columns, depending on the join type. A COUNT(*) or other aggregate function over the orphaned child table will count the orphans. If the parent primary keys get re-used for new rows the orphaned children may end up pointing to the wrong parent. Databases have things like foreign keys to prevent anomalies like this, you shouldn't just conclude it's not worth the trouble now because you will deal with that mistake later.

You assume you will deal with mistakes later and that will be more costly than the effort implement foreign keys. That's a pretty big assumption that has been proven to be false in many cases.

Implementing foreign key constraints requires next to no effort -- a single declaration in SQL. You need to think through the schema and the relationships but you have to do that in any case.

"Proven to be false in many cases" with not a single example is just an opinion without evidence. The benefit of foreign keys has decades of actual documentation.

I'm going to assume you didn't read the linked thread because right there are examples of both.

Assume what you want. Since I specifically mentioned migrations as a not so good reason to avoid foreign keys, in my opinion, I would think it's obvious I read the original article.

Sanely created schema does not have these kinds of problems ;). Plus FKs are a performance impact.

The most trivial schema with a parent-child relationship either has foreign key constraints or update anomalies. There's no way to "sanely create" a normalized schema that doesn't have such relationships.

Performance impact may or may not matter. You don't discard valuable techniques and decades of experience for a "might happen." You measure and identify actual performance and adjust with facts. All indexes have both a performance benefit and a performance cost, incurred in the first case when selecting, and in the second case when updating. The performance benefit and cost of foreign keys comes from the index created. You get protection from update anomalies almost for free.

Everyone comes from a different backround and experience. You are wearing a hard hat, but if you don't have the resources, then you need to be smart and not just belch up the dogmas/theoretical stuff.

Are we discussing constraints, or keys? The term “foreign key” without the word “constraint” totally changes the meaning.

From 4 years ago on the linked page:

“Some comments in the HN thread seem to confuse the logical referential integrity of the relational model, and the FOREIGN KEY constraint, which is a construct where the database enforces the referential integrity.

We do use tables with ‘id’s and tables referencing those ‘id’s. We do not use FOREIGN KEY constraints in our table definitions.”

As a DBA I feel is this a bit of hand-waving… sure you can say your database model has logical referential integrity: “Parent 1:N Child” but without a referential constraint at the database level it’s just documentation.

Previous discussion (2019): https://news.ycombinator.com/item?id=21486494

Reply on news.ycombinator.com