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

151–160 of 337 posts

Re: We do not use foreign keys (2016)

#151

Post by Hamuko »

zabzonk wrote: post #114
Hamuko wrote: post #70

Earlier quoted context omitted.

It's not like you can avoid incorrect data just with foreign keys. Say you have an invoice model. There's a boolean value indicating that the invoice is final and a numerical value for invoice number. Final invoices must have invoice numbers. But a bug in your system manages to update an invoice so that it's final, but missing an invoice number. That row is incorrect and it's gonna cause an issue somewhere. I can onl…

How would the system update the invoice at all without an invoice number?

I'm not sure if I understand.

Re: We do not use foreign keys (2016)

#152

Post by spookthesunset »

dguo wrote: post #56
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.

In what way does sharing make it impossible to have database enforced referential integrity?

Re: We do not use foreign keys (2016)

#153

Post by taurath »

resters wrote: post #130
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…

> 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. This point is valid in the same way as telling a startup founder to give up the startup and invest in the S&P 500. It's trivially true, but ultimately useless advice. Building technology is hard precisely because there are so many tradeoffs. Speed to market vs sc…

I didn't say never care about scale in the early stage. I've found it a more common problem that people in startups are building systems with too much complexity to solve problems they faced at previous employers, in order to "save themselves time" in the future that ends up never coming. There are certainly some technology companies for which scalability is the most important thing - Instagram being the biggest example I can think of. The point of me saying so was to remind people - despite what you may think, you are not an Instagram, and more than likely your business doesn't require you to be one.

Plenty of early stage tech leads DO use wordpress and rails because their company doesn't need something more. You just don't hear about them often because they won't be sexy enough for HN. YAGNI.

Re: We do not use foreign keys (2016)

#154

Post by geophile »

Hamuko wrote: post #70
geophile wrote: post #32

Wow. No discussion at all regarding the correctness of their databases. Is all the data correct, or are there FKs referencing missing PKs? Actually, even discovering this would be difficult, since any FK violation could be just viewing the middle of what would be a consistent update in flight. I get that cross-shard FKs are especially difficult. But any discussion of this topic without addressing correctness concerns…

It's not like you can avoid incorrect data just with foreign keys. Say you have an invoice model. There's a boolean value indicating that the invoice is final and a numerical value for invoice number. Final invoices must have invoice numbers. But a bug in your system manages to update an invoice so that it's final, but missing an invoice number. That row is incorrect and it's gonna cause an issue somewhere. I can onl…

What is your point? That database constraints cannot catch all errors, so don’t even bother with the ones they can catch?

Re: We do not use foreign keys (2016)

#155

Post by GauntletWizard »

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…

You can keep saying that, but it does not make it true. Properly modeled data does not need those constraints. Well written software handles these correctly. Mediocrely written software fails and complains loudly. Badly written software might get hosed. You're at just as much risk (or more, in my opinion) of that with bad schema changes as with not having foreign key constraints.

Re: We do not use foreign keys (2016)

#156

Post by evanelias »

shawnz wrote: post #16
brobinson wrote: post #6

Earlier quoted context omitted.

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

So then don't do it inside a transaction. It still wouldn't be any less safe than their current approach of just not using FKs at all

Adding a foreign key to a massive table can take a long time, since all existing row data must be validated against the constraint. With logical replication, long operations like this can be quite disruptive, even with a multi-threaded logical replication scheme.

Re: We do not use foreign keys (2016)

#157

Post by Gibbon1 »

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.

Thing I've tried and mostly failed to get across. There is usually plenty of money and resources to throw at products that are making money. Vs the amount for those that aren't.

As long as you're not painting yourself into a corner a lot of things can be fixed 'later'. Later when your not desperate to get the thing off the ground. Later when you can throw two engineers at it for six months. Later when the pain points are well understood.

Re: We do not use foreign keys (2016)

#158

Post by smt88 »

SkyPuncher wrote: post #124
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.

There are many cases where relational integrity does NOT need to be enforced and in fact can happen in the application level. One of the big reasons NoSQL got popular was because it broke the norms around strict data relationships.

And one of the reasons NoSQL didn't revolutionize anything is because people realized it's worthwhile to put on your seatbelt. It's either arrogant or naive to think that automated safety measures aren't going to catch any of one's mistakes.

Re: We do not use foreign keys (2016)

#159

Post by hnarn »

spookthesunset wrote: post #122
benburleson wrote: post #105

Earlier quoted context omitted.

`malloc` won't fail, right?

the javascript will enforce the user input. no need to have the backend check it again!

This should be a joke, but considering how often it's actually the case it's unfortunately not that funny.

Re: We do not use foreign keys (2016)

#160

Post by spookthesunset »

Hamuko wrote: post #61
brlewis wrote: post #31

> It may even rely on FK to cascade deletes (shudder) Is this just the author's personal taste, or is there something about mysql that makes ON DELETE CASCADE a bad idea? In postgresql it's a useful tool for maintaining database consistency.

I'm guessing it's the possibility for data loss. So someone accidentally deletes a user and it cascade deletes all invoices referenced to that user (deleting invoices is a big no-no in accounting).

These kinds of things are exactly why foreign keys are so helpful to keep things from getting corrupted. Invoices should always reference a user in the user table.

The relationship should not be "ON DELETE CASCADE" but probably "ON DELETE NO ACTION". Then when some junior dev tries to issue a DELETE on the users table, the DB will correctly tell them to get bent and throw an error because there are dangling invoices still associated with the user. Because you are a smart person, you've also prohibited DELETE and UPDATE's against the invoices table for the db user that is used. The application cannot delete the invoice, and it cannot delete the user either--which is the only correct course of action. The database can now protect itself from hostile input from all your junior devs. Exactly why you want FK's. You can't trust user input.

The fact that the author of the article shudders about cascading deletes demonstrates that they have zero authority to speak about databases--something that is very common in the developer community. DB's aren't mysterious black boxes. They are really fucking cool tools that can do all kinds of neat shit to keep your data nice, safe, cozy and warm (they do way more than that too... a good database is perhaps one of the most underutilized tools developers have in their toolbox)

Post reply on HN