Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We do not use foreign keys (2016)

github.com

Post reply on HN
Print view View original

51–60 of 337 posts

Re: We do not use foreign keys (2016)

#51

Post by mumblemumble »

jayd16 wrote: post #37
mumblemumble wrote: post #21

Earlier quoted context omitted.

I think the emphasis there is the word "online". The method you propose might work, but not necessarily well. Catching missing primary keys at the end is a problem because then you have a bunch of data integrity issues to sort out, preventing you from re-applying the constraint, in a live system where apps are relying on the database to do integrity checks, meaning that there's a decent chance that the running system…

I'm not sure "you can't add checks because they might fail!" is sound reasoning...

It's not "you can't add checks because they might fail"; it's "you can't temporarily remove checks in a live system where the database's users are used to leaning on them, because when you try to re-apply them you just might find yourself in a world of fail".

Re: We do not use foreign keys (2016)

#52

Post by eternalban »

PopeDotNinja wrote: post #22

Because I've never thought about this, I'll ask the dumb question... A shopping cart has many items. An item belongs to a shopping cart. In a relational database, without foreign keys, how do you associate the shopping cart with the items?

A FK declaration uses named field(s) in your table. You can omit the FK but the associative fields remain. An FK is a constraint on what values can be in those fields.

Re: We do not use foreign keys (2016)

#54

Post by Hamuko »

shayanjavadi wrote: post #23

Genuine question, what are the alternatives?

You have columns that refer to the PKs of other tables, just without constraints. So your invoice table would have a column `user_id`, which tells you which user the invoice is for. But if you enter an invoice for user ID 999,999 when you have no users, the database eats it happily, since it has no opinion as to what that data should be.

Re: We do not use foreign keys (2016)

#55

Post by munchbunny »

conroy wrote: post #5

When posts like these come up, I'd like to remind people that context matters when making technical decisions. What works for large companies with huge scale (GitHub, Google, Facebook) may not work for you. As a counter point to the linked issue, I operate a few small applications. Foreign-keys (and constraints in general) are great at ensuring that invalid data doesn't find its way into your database. Yes, they have…

Someone two degrees of separation from me coined the term "medium data". I don't remember their name, but I absolutely love the term.

You are "big data" when black swan events (like hardware failure taking down a database node) become regular enough that you begin to statistically model it. Before that, you might just have a lot of data, but you aren't having "big data" problems.

Re: We do not use foreign keys (2016)

#56

Post by dguo »

PopeDotNinja wrote: post #22

Because I've never thought about this, I'll ask the dumb question... A shopping cart has many items. An item belongs to a shopping cart. In a relational database, without foreign keys, how do you associate the shopping cart with the items?

The association would still exist implicitly in the code that uses the database. The columns could stay the same, and the code could still use the item's hypothetical cart_id column to get the items in a cart.

You lose the explicitness and safety of referential integrity in the database, but it's a trade off that can enable other things, like sharding, as mentioned by the GitHub employee.

Re: We do not use foreign keys (2016)

#58

Post by blantonl »

conroy wrote: post #5

When posts like these come up, I'd like to remind people that context matters when making technical decisions. What works for large companies with huge scale (GitHub, Google, Facebook) may not work for you. As a counter point to the linked issue, I operate a few small applications. Foreign-keys (and constraints in general) are great at ensuring that invalid data doesn't find its way into your database. Yes, they have…

When you get to a point where you need to start sharding your database, congratulate yourselves for the enormous success and toast the night to come.

The next morning you can embark upon the engineering effort to start the sharding re-architecture.

Re: We do not use foreign keys (2016)

#59

Post by munk-a »

dkhenry wrote: post #44
spookthesunset wrote: post #27

The number of times I’ve seen serious data corruption because “foreign keys are bad and we can just enforce it in code” is amazing. There is zero excuse to not use FK’s Any database that doesn’t use FK’s is almost guaranteed to have crap in it that didn’t get cleaned up, resulting in data corruption (and yes, dangling stuff in tables count as data corruption). Developers aren’t perfect. Shit will slip through even wi…

Every time I have seen a database use foreign keys there has been data corruption, because everyone thought foreign keys were declarative and not procedural. Just because you have a foreign key doesn't mean it was always there or that it applied on every transaction. You can turn them off at the connection level and you in fact must turn them off for almost any kind of bulk data load. You should read shlomi's post he…

I am a bit doubtful of this, especially within PostgreSQL you need to specifically go out of your way to create a NOT VALID constraint. I know that MySQL of old would default to an engine that didn't actually enforce key relationships (which was terrible but at least well documented) but in the modern world DBs will tend toward enforcement unless you specifically work against it.

Re: We do not use foreign keys (2016)

#60

Post by brlewis »

hw wrote: post #28
andreimatei1 wrote: post #13

At GitHub they should be using CockroachDB (:P), a scalable relational database that also supports schema changes. Then their FKs would work just fine (albeit with some performance implications).

Or, just not use FKs at all. There are many factors to consider when deciding on a DB, and being able to use FKs is probably not high on the list.

>being able to use FKs is probably not high on the list.

No, that's not at all probable. For many, "database" is synonymous with "ACID database", for which foreign keys are important. https://en.wikipedia.org/wiki/ACID#Consistency

Post reply on HN