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

131–140 of 337 posts

Re: We do not use foreign keys (2016)

#131

Post by andrewfromx »

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…

The engineer 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!

Re: We do not use foreign keys (2016)

#132

Post by matt4077 »

spookthesunset wrote: post #97
GauntletWizard wrote: post #67

Earlier quoted context omitted.

There are 100% foreign key violations in their database. That is not the same as their database being corrupt. They have engineered for, and understand the implications of, foreign key violations. Typically, it's as simple as "This row can be deleted", and that can cascade - at a totally different rate than you'd find in a database and with totally different performance characteristics.

Foreign key violations are data corruption!!! It violates the rules of how the data relates and can and will screw up any number of things that depend on the rules being enforced. Reporting and bi data might get hosed. Account management might get hosed. Who knows what happens when FK rules are violated because by definition they should never be violated. It puts all applications on top into a undefined state, leadin…

> Foreign key violations are 100% data corruption

Sure, yes, of course, who cares?

These are self-imposed rules, so breaking them is wholly up to yourself. And there are trade-offs involved that may often make it acceptable relax them.

You seem to be rather invested in one set of arbitrary definitions. If people do fine even in situations where they "by definition" shouldn't, it's the definitions that have been found lacking, not the people.

Re: We do not use foreign keys (2016)

#133

Post by [deleted] »

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…

[deleted]

Re: We do not use foreign keys (2016)

#134

Post by spookthesunset »

whalesalad wrote: post #117
spookthesunset wrote: post #104

Earlier quoted context omitted.

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…

Yikes. Someone got up on the wrong side of bed today!

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

Re: We do not use foreign keys (2016)

#135

Post by rhinoceraptor »

spookthesunset wrote: post #62
dkhenry wrote: post #44

Earlier quoted context omitted.

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…

Maybe 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 yo…

> MySQL

What other database has been used at such a giant scale at so many companies, than MySQL? I'm sure Oracle and SQL Server are used at big companies, but nowhere near Facebook scale.

Re: We do not use foreign keys (2016)

#136

Post by lmm »

Diggsey wrote: post #121
spookthesunset wrote: post #46

Earlier quoted context omitted.

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.

This is not remotely true. You can do this with pretty much any database that supports SERIALIZABLE isolation-level transactions correctly. The point is that application code has bugs, and it's a lot easier to specify your constraints declaratively in a single place using a purpose-built DSL (SQL) than it is to enforce them procedurally every time you access the database.

I'd argue that any decent application programming language can express these constraints better than SQL can.

Re: We do not use foreign keys (2016)

#137

Post by perlgeek »

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?

In a non-relational model, you'd generally copy the items/products and their quantity into the shopping cart.

That's a huge change from the relational model, and takes much more space than a normalized approach, but it also comes with some advantages. For example, it makes sure that the price of the items in the cart remain constant, even when you change product prices. That way, you don't have to inform the user "oh, your cart just got 8% more expensive" before checkout.

Re: We do not use foreign keys (2016)

#138

Post by jrochkind1 »

spookthesunset wrote: post #81
munk-a wrote: post #49

Earlier quoted context omitted.

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…

> 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 ha…

Unique constraint is a good example, because it reminds us about race conditions.

The app can check that it won't violate a unique constraint before doing an insert/update, but in between that check and actually doing the insert/update, some other process may have changed data, such that unique constraint can be violated.

So when the rdbms catches this, it'snot just a "fuck you for giving me bad data" condition if the implication there is that it was a bug in app code, and it's a failsafe. It isn't necessarily a bug at all -- unless you intended it to be the app's responsibility to use db-level locks and/or transactions to guarantee this can't happen without the uniqueness constraint -- but then why not just use a uniqueness constraint, the tool the db is actually giving you for this?

Mature rdbms's sometimes don't get the recognition they deserve for being amazing at enforcing data consistency and integrity under concurrency, with pretty reasonable performance for a wide variety of usage patterns.

Foreign key constraint can be similar; you can do all the checking you want, but some other process can delete the object with the pk right before you set the fk to it.

If you have app usage patterns such that you really can't afford database data integrity enforcement (what level of use that is of course depends on your rdbms)... you are forced to give up a lot of things the rdbms is really good at, and reinvent them (in a way that is more performant than the db??) or figure out how to make sure all code written never actually assumes consistent data.

Re: We do not use foreign keys (2016)

#139

Post by [deleted] »

whalesalad wrote: post #117
spookthesunset wrote: post #104

Earlier quoted context omitted.

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…

Yikes. Someone got up on the wrong side of bed today!

[deleted]

Re: We do not use foreign keys (2016)

#140

Post by evanelias »

zerg2k wrote: post #14
brobinson wrote: post #6

Earlier quoted context omitted.

>preferably inside a transaction? MySQL does not support transactional DDL, unfortunately.

That’s no longer true since MySQL8 https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html

MySQL 8 DDL is atomic at the statement level, but cannot be combined with other SQL DDL or DML statements in transactions yet.
Post reply on HN