7 ms·
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
by spookthesunset 7y ago
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 with the most rigorous process. The database should always provide tools to keep its self from getting corrupted. This is why good database systems have constraints like FK’s. If your database software makes using those tools painful (cough MySQL), get a better database system.
Not using FK’s is just plain old ignorance.
- Litmus2336 7y agoSeriously. "As a C developer, I never check exit codes of child processes. We can just enforce it by ensuring child processes don't have bugs"
- benburleson 7y ago`malloc` won't fail, right?
- spookthesunset 7y agothe javascript will enforce the user input. no need to have the backend check it again!
- hnarn 7y agoThis should be a joke, but considering how often it's actually the case it's unfortunately not that funny.
- cryptonector 7y agoIt won't on Linux! But hey, random processes will get OOM-killed.
- zenhack 7y agoYou can turn off overcommit, in which case you'll get a null return instead of OOM killing. But also, you can still get a malloc failure without actually be running out of memory if the allocator can't find a big enough contiguous chunk of address space. This is highly unlikely on a 64 bit system, but if you try to malloc gigabytes on a 32 bit machine you might see it.
- Iv 7y agoAnd also, you never know when this theoretical case actually happens but it did happen to me: at one point someone may use your code on an Arduino and unexpectedly, it works! But memory allocation could fail more than you expect!
- NullPrefix 7y agoOn Unix it won't. You can overcommit memory and only get into trouble when you actually try to page it. But not at malloc time.
- patrickthebold 7y agoIirc that's configurable.
- ci5er 7y agoNot as much as developers who can't find their own coding errors might have you think!
- dkhenry 7y agoEvery 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.
- munk-a 7y agoI 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.
- spookthesunset 7y agoMaybe shitty toy database systems like MySQL let you disable constraints on a per session basis. A real database system might let you defer them until the end of a transaction—which is the only correct way to operate. Once you commit that transaction, what you put into the system better fucking make sense and it is the job of the database system (and only the database system) to enforce that. Like I said before if your database system makes using constraints hard or lets you shoot yourself in the foot (lol at disabling constraints per session), don’t just walk but run away from that system.
- mixedCase 7y ago> Maybe shitty toy database systems like MySQL While appropiate to define in few words some of MySQL's colossal mistakes, this isn't the kind of language that will sway heads that have been comfortably using MySQL because those defects are just "what DB's do".
- 7y ago
- munk-a 7y agoI 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.
- spookthesunset 7y ago> 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. There are two kinds of error handling. The “happy error” and the “fuck you asshole” error handing. Good systems have both. Yes, the application should check it isn’t about to violate a FK constraint. Just like it usually checks that it isn’t about to violate a unique constraint. That way the application can fail in happy, controlled way. But if the application doesnt do the right thing than the DB server still should puke all over the transaction with a “fuck you, don’t feed me bullshit” error. Just like data entry over the web. The javascript can return all kinds of nice happy messages to the user about missing fields and stuff. But the backend should always validate and enforce correctness even if it’s mode of failure is some ugly “piss off, don’t feed me crap” message. Just like you should never trust user provided data coming from an HTTP post, a database should never trust the INSERT or UPDATE is valid and won’t corrupt the database. Both backend systems can return mean old ugly errors when shit is bad and let the front end do pre-validation that can do happy nice errors. The backend always has to enforce its own data integrity. Period.
- whalesalad 7y agoEh. The issue arises when you have a very poorly designed schema and missing application-level operations to perform cleanup and deletion. You don’t want to be caught in that situation because then you are handcuffed and cannot clean the database properly. For example, from the perspective of business operations you might have something that creates 1 single thing where under the hood 5+ DB objects are all created in a transactional manner and a very specific order. So how do you unroll that? You gotta carefully construct the rollback commands or just get rid of the bowling lane bumpers and slash and burn. So you gotta commit and go all-in. If you make the choice to do it all within the app later, at least you keep that flexibility. It all depends on your processes and your team. If you don’t design things properly and institute the correct procedures for designing and operating the DB, having or not having constraints at the DB level is inconsequential to the other issues you will face.
- spookthesunset 7y agoBullshit. Even with the most perfect developers and perfect system, letting application code enforce constraints will lead to data corruption period. Some application will crash or something and leave dangling garbage behind and boom you are fucked. Would you ever trust that form data is valid because the JavaScript front end “validated” it? No! Why the hell are you going to trust that everything sent to the database is valid? No constraints like FK’s is exactly like not validating input because “the JavaScript layer got it”. It is out of ignorance that people argue otherwise, sorry. Too many people don’t understand relational database...
- whalesalad 7y agoYikes. Someone got up on the wrong side of bed today!
- spookthesunset 7y agoSorry. I have just seen way to many systems fail in way too many mysterious ways because "foreign keys are bad" and "the application code can do the validation". In fact, I wager that 100% of all databases that don't have FK's have some kind of data corruption that is causing at least some kind of user-visible issues. It is frustrating to me because even the most green behind the error developer would cringe at somebody saying "we don't need the backend to validate user input because the front-end javascript does it for us". This is the same thing. Never trust your inputs. Your application code calling the database server is exactly like some javascript client calling your backend system. Your database has tools to keep it from being fed bullshit input. For some extremely frustrating reason, people think it is perfectly okay to have the database trust its user input. The result is in almost every single instance where those tools aren't used, the inevitable corrupt data will cause some kind of hard to reproduce user-impacting issue. I've seen it so many times it makes my head hurt.
- EugeneOZ 7y ago> and yes, dangling stuff in tables count as data corruption No. There are exceptions like financial systems, but most of the time it doesn't matter if your DB has dead links to some removed entities. > Not using FK’s is just plain old ignorance. Nice level of argumentation, but I prefer the way how author from github can prove his opinion.
- andrewfromx 7y agoThe engineer https://en.wikipedia.org/wiki/Niki_Lauda https://en.wikipedia.org/wiki/Niki_Lauda from Rush movie fame, if he's working on a race-car-level-system where every little bit of performance you can tweak out counts, like wringing a towel dry, every last bit... and in that mindset, FKs are removed NOT from ignorance!
- rhinoceraptor 7y agoI had a job where the database was intentionally lacking FKs so that users could input data out of order, for example create a shipping route Foo that goes to a warehouse Bar, before the warehouse existed. Let's just say it was a suboptimal design.
- cryptonector 7y agoNot using FKs essentially means eventual consistency, and it's on you to ensure it. Obviously, eventual consistency is something a lot of systems have to settle for, often for reasons that have nothing to do with architecture, but just because of circumstances where you have to synchronize disparate systems and there's no way to eliminate all but one. I'll note that there are very large systems that do provide ACID-like consistency (e.g., Spanner, Lustre), so it's not at all that "large -> eventual consistency". I won't pass judgment on projects where eventual consistency was a given from the start, even if I might think they could have done better, but too, I wouldn't make either approach a hard and fast rule: circumstances vary. What bothers me is the extent to which I see "triggers bad", "FKs bad", "business logic in the DB bad" blanket statements out there.
- Aeolun 7y agoThe disdain some developers tend to have for their data storage system is pretty unreal. It’s like they see it as a necessary evil instead of their friend. They often react to FK’s as if they cramp their style, where their style is to just insert whatever into the database without regards for what And where it is.
- macspoofing 7y agoAt the scale of GitHub, FKs may very well being more problems than benefits. For everyone else, FKs are a benefit.
- Iv 7y agoThanks. As a mere occasional user of databases I was confused and trying to figure out how you could not use FKs without recreating essentially the same thing in code.