[deleted]
We do not use foreign keys (2016)
41–50 of 337 posts
Re: We do not use foreign keys (2016)
#42Because 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?
There is no difference when not having foreign key constraints.
In both cases you can't delete items from your catalog or you'd break old carts. So you just don't do it.
Another way is to copy the product from the product table to the cart table. This saves you from making references to versions of products.
Re: We do not use foreign keys (2016)
#43I thought foreign keys were necessary for the consistency part of ACID compliance. Once you remove the integrity constraint, the database cannot guarantee consistency, even with isolation and atomicity.
Sure, removing the constraint will get you a huge burst in performance, but now you are in charge of guaranteeing consistency outside of transactions in a distributed system involving at least one client and one server. That's a tall order. As Runar Bjarnson put it: "Constraints liberate, liberties constrain,"[1]. If you give something the freedom to be inconsistent at any extreme scale, Murphy's law requires that anything that can go wrong, will go wrong. It is hubris to think that the average developer who hasn't spent years becoming a consistency expert is going to be able to guarantee that data won't become corrupted better than the engineers that build and maintain and acacademics that study rdbms systems full-time.Even experts get this wrong all the time [2]. To all junior devs out there, until you know you need to abandon a guarantee available to you in a given system, exhaust every other possible option.
1. https://youtu.be/GqmsQeSzMdw 2. https://aphyr.com/posts/282-jepsen-postgres
Re: We do not use foreign keys (2016)
#44The 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 gives good reasons. Specifically this is a github issue on his tool gh-ost which is for online schema migration, and FK's pose lots of problems for online schema migrations.
Re: We do not use foreign keys (2016)
#45Because 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?
[deleted]
Re: We do not use foreign keys (2016)
#46How about this: if your service gets as big as github, then maybe consider doing odd things to eke out more performance or shard or whatever. Otherwise: use FK's to maintain stronger data integrity. As hesk mentions below, in Postgres, you can do all kinds of table ALTERing if needs be.
FK’s don’t just maintain “stronger” data integrity, they are the only way to maintain relational data integrity. Application code cannot maintain that relational integrity, period.
Any developer who thinks application code can enforce relational integrity is naive and does not understand relational database systems. It is impossible for any layer above the database itself to keep things from getting corrupt.
Re: We do not use foreign keys (2016)
#47[deleted]
Re: We do not use foreign keys (2016)
#48When 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…
Came here to say something like this, though you said it more eloquently. Also a couple replies to you below add great context.
Re: We do not use foreign keys (2016)
#49The 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…
I think this is a bit hyperbolic of an expression, but I do want to reinforce that all applications need to confirm data integrity, you either confirm it when saving the data, when extracting the data, or have to very carefully balance various consistency concerns and either enforce consistency before saving data or enforce consistency after saving (but before extracting) data.
Things like RDBMS's provide some really nice utilities for data consistency enforcement in the form of FKs, these are not the most performant approaches in all cases but they're optimized to be good for nearly all use cases.
If your business need /Requires/ moving off of FKs onto some other data integrity guarantee, then just understand that you're going to be reinventing the wheel. It might turn out to be a better wheel for your car, but it's going to be expensive. And... unless you specifically hire specialized people who comprehend data guarantees and ACID properties, you'll probably do it wrong. This sort of an undertaking is for mature companies only.
Re: We do not use foreign keys (2016)
#50When 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…
Two of Google's big internal databases don't use foreign keys (Spanner[0] and F1 [1]), rather they use hierarchies (parent/child tables). It definitely limits you a lot more than foreign keys, but seems to make sharding easier. It works well for some use cases, but takes some getting used to. Cockroachdb also has this functionality [2].
[0] https://cloud.google.com/spanner/docs/schema-and-data-model
[1] https://ai.google/research/pubs/pub41344
[2] https://www.cockroachlabs.com/docs/stable/interleave-in-pare...