We do not use foreign keys (2016) • Hacker News Board

Re: We do not use foreign keys (2016)

#101

Post by dekhn » Fri, Nov 08, 2019, 9:57 PM UTC

josteink wrote: post #94

dekhn wrote: post #65

I have been perpetually annoyed at the SQL/RDBMS/relational calculus model. It always feels like a huge context shift from imperative programming. after many years of writing SQL, I noticed that many other people end up writing SQL statements that look more or less like computer programs (CASE statements, subselects, etc). It all came to a head when I naively asked an experienced SQL developer how to represent a tree…

> I did a deeper dive I learned that tree representations in SQL are a rabbit hole of insanity all arranged around referential integrity. It’s not crazy, just impractical. Trees are mostly represented the same way in memory, except you don’t have to access them in table-format, which admittedly for tree-data is very, very cumbersome.

I'm not sure what data structure/language environment you mean, but in programming languages, trees are normally represented using pointers (typically from the parent to the child and back).

All of this literally goes back to hierarchical databases which were mostly replaces by relational databases for what appear to be mainly performance and implementation reasons.

Re: We do not use foreign keys (2016)

#102

Post by [deleted] » Fri, Nov 08, 2019, 9:59 PM UTC

opmac wrote: post #64

taurath wrote: post #25

Earlier quoted context omitted.

In the same vein, I'd like to remind people that you are probably not a "temporarily low-scale big-data company", in the same vein as a temporarily embarrassed millionaire. In lots of cases going for the very long term scalable solution will be an impediment to your growth, and I'd suggest dealing with those issues when the chance that you need them is on the horizon, rather than across the globe. CQRS is one of the…

YAGNI - learn to embrace it.

[deleted]

Re: We do not use foreign keys (2016)

#103

Post by camnora » Fri, Nov 08, 2019, 10:00 PM UTC

dekhn wrote: post #65

I have been perpetually annoyed at the SQL/RDBMS/relational calculus model. It always feels like a huge context shift from imperative programming. after many years of writing SQL, I noticed that many other people end up writing SQL statements that look more or less like computer programs (CASE statements, subselects, etc). It all came to a head when I naively asked an experienced SQL developer how to represent a tree…

An alternative is using a nested set https://en.m.wikipedia.org/wiki/Nested_set_model

Re: We do not use foreign keys (2016)

#104

Post by spookthesunset » Fri, Nov 08, 2019, 10:00 PM UTC

whalesalad wrote: post #75

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…

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

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

Re: We do not use foreign keys (2016)

#105

Post by benburleson » Fri, Nov 08, 2019, 10:04 PM UTC

Litmus2336 wrote: post #35

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…

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

`malloc` won't fail, right?

Re: We do not use foreign keys (2016)

#106

Post by anshul008 » Fri, Nov 08, 2019, 10:04 PM UTC

The performance of my team's application at Myntra (Bangalore) was badly affected by foreign keys on MySQL and we decided to drop them.

The trick we used was to drop them only in production, not in test environments. In test environments they acted as guardrails to ensure that our application did not break the constraint.

So in a way it gave us best of both worlds

Re: We do not use foreign keys (2016)

#107

Post by hrktb » Fri, Nov 08, 2019, 10:05 PM UTC

sturgill wrote: post #78

DaiPlusPlus wrote: post #20

Earlier quoted context omitted.

As someone currently fighting a battle with an LoB application written without foreign-key constraints with hundreds of thousands of rows of corrupted data because of bugs in sprocs that assigned the wrong value to the wrong foreign key column because they were similarly named - THIS! The reply in the GitHub thread we’re talking about makes it clear that they still perform FK validation - it’s just performed in the a…

I’m pretty confident that GitHub doesn’t use foreign keys because it was built as a Rails app. And the “Rails Way” is to create these constraints in the model. Foreign key constraints weren’t a first-class member in Rails until v4 (if memory serves correctly). I was once a full-time Rails dev and really loved the framework (I don’t write as many user facing applications these days). Most of the Omakase trade offs did…

It’s at its core a case by case decision, I think FK are also a net negative in data ingestion scenari where the data set is big enough.

Trying to make sure everything is where it needs to be at any given time, everything is inserted in the right order and the data is always consistent brings exponential amount of conplexity when it could all be checked at the end and pruned for invalid data. And usually DB integrity will not be enough, you’ll want business level validation that all is OK, so there will be app level checks anyway.

Re: We do not use foreign keys (2016)

#108

Post by baby » Fri, Nov 08, 2019, 10:05 PM UTC

Never used them in my personal projects either eventhough I learned about them in school. It just didn’t make sense to me, why would I constrain myself in the database when I could manage all of this in the application logic?

And if you want to experiment with different schema it’s just a nightmare.

Re: We do not use foreign keys (2016)

#109

Post by dekhn » Fri, Nov 08, 2019, 10:07 PM UTC

camnora wrote: post #103

dekhn wrote: post #65

I have been perpetually annoyed at the SQL/RDBMS/relational calculus model. It always feels like a huge context shift from imperative programming. after many years of writing SQL, I noticed that many other people end up writing SQL statements that look more or less like computer programs (CASE statements, subselects, etc). It all came to a head when I naively asked an experienced SQL developer how to represent a tree…

An alternative is using a nested set https://en.m.wikipedia.org/wiki/Nested_set_model

"Updating requires renumbering and is therefore expensive".

Re: We do not use foreign keys (2016)

#110

Post by swasheck » Fri, Nov 08, 2019, 10:07 PM UTC

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…

Additionally, at least in Sql Server, trusted foreign keys can give the CBO more, good options because of the guaranteed referential integrity on reads. FKs are killer on larger writes, though.