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

Re: We do not use foreign keys (2016)

#211

Post by lugg » Fri, Nov 08, 2019, 11:29 PM UTC

spookthesunset wrote: post #34

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…

The application code isn’t enforcing FK constraints because that is impossible for the application to do. Their database is almost 100% guaranteed to be corrupt as a result. Application code has bugs. Application code can fail in ways that result in corruption. A primary job of the database is to keep itself from getting corrupted. Enforcing foreign key violations is only something the database can do correctly. Punt…

Will result in corruption. Yes.

Will result in more fault tolerant software, also yes.

It's a trade off and one I make willingly at every scale.

I stopped using foreign keys after university and have never wanted them since.

Non nullable database fields are far more useful than worrying about fks.

Re: We do not use foreign keys (2016)

#212

Post by barrkel » Fri, Nov 08, 2019, 11:30 PM UTC

munk-a wrote: post #83

jagged-chisel wrote: post #10

Worked at a bank years ago. One of their DBAs eschewed FKs in production. Databases were designed with FKs which were enforced in Dev and QA. If your app survived testing (automated, QA team ... the entire gamut) without producing FK violation exceptions, your app could be promoted to production, where FKs were not enforced, making things pretty fast. I feel like this was a stopgap on the way to eliminating FKs. I ha…

This approach is quite scary to me and I would have argued very vocally against their rejections of FKs. There are ways to rely on replication for read serving, periodic disabling of FKs during batch inserts, FK integrity checks on replications, ... these can all address the performance issues inherent in FKs, it's also (generally) quite possible to attempt to architecturally disentangle too large networks of interde…

FKs add read locks to referenced rows. It limits concurrency and it is observable. FKs constrain your ability to incrementally widen 32-bit FKs once you go over 2 billion rows, if you start out with 32-bit PKs. Two concrete reasons to avoid FKs in production, or at least disable them for longer running transactions.

I think this perspective is something you only get once you've run bigger databases in production.

Re: We do not use foreign keys (2016)

#213

Post by lugg » Fri, Nov 08, 2019, 11:31 PM UTC

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…

This assumes things depend on the rule being enforced.

Nobody suggests taking an app / db with foreign keys and removing them that is a recipe for disaster.

Re: We do not use foreign keys (2016)

#214

Post by lugg » Fri, Nov 08, 2019, 11:31 PM UTC

Aeolun wrote: post #174

SkyPuncher wrote: post #118

Earlier quoted context omitted.

> It violates the rules of how the data relates You're missing an important point, it violates a certain set of rules of how the data relates. That certain set of rules typically makes it easier to write most general data applications. If your rules define a foreign key as optional, you can easily engineer an application around it. Great examples include NoSQL and Ruby duck-typing. In both cases, you infer actions ba…

The whole fact that you made a foreign key implies that that is now a rule of your database. If anything is in that column that does not adhere to the foreign key, that means your database is corrupted.

Yes, and what if you don't make a foreign key?

Re: We do not use foreign keys (2016)

#215

Post by Quekid5 » Fri, Nov 08, 2019, 11:32 PM UTC

corebit wrote: post #71

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…

The biggest issue with CQRS I've seen is people thinking CQRS means you need multiple, duplicate data structures, mappers, a few Kafka topics and a PhD, when IN REALITY all it means is you put methods that return data without modifying it in one interface/class and methods that have side effects in another interface/class - which is really just a good application of interface segregation. Moreover, you now have a gre…

Like you, I've found the general idea of CQRS/ES[0] incredibly valuable because it both enforces a functional approach to state (e.g. 'current state' is a fold over events), and it forces people to really think about the domain. (E.g. which bits are really atomic units, or what can be fixed up if it goes wrong in some way.)

It also forces people to think about something that's usually glossed over: Consistency. If you have an RDBMS backing you, you tend to not think about the fact that what the user on a web page sees is already out of date when they see it, so any responsible application should track when that data was read and reject updates if the backing data has changed since it was read... but very few applications even attempt to do this (it's really hard and a huge amount of boilerplate with most APIs). With CQRS/ES you are really forced to think about these things up front -- and you can actually avoid most of the issues. Whether that increases or decreases 'productivity' I don't know, but I do know that thinking about these things increases correctness.

For me, correctness is paramount. If you don't mind bugs, I can give you an infinitely fast solution.

[0] I do think ES is an integral component.

Re: We do not use foreign keys (2016)

#216

Post by dkhenry » Fri, Nov 08, 2019, 11:33 PM UTC

papln wrote: post #189

dkhenry wrote: post #173

Earlier quoted context omitted.

This really makes it seem like you have never used a database system at scale. There are reasons why systems like MySQL let you turn them off, and they are some of the same reasons why pretty much everyone who uses a database at scale has settled on MySQL. Also its why as is mentioned in the issue once you actually scale your database foreign keys become a nightmare. If all you have is a toy project you can feel free…

> everyone who uses a database at scale has settled on MySQL hmm? I've only every seen people using MySQL at scale if they started with MySQL in prototype and never had the energy to migrate.

Well here is one case of a large scale user migrating

https://eng.uber.com/mysql-migration/

And there are tons more. In fact here is a tool to help you do it

https://github.com/pivotal-cf/pg2mysql

Re: We do not use foreign keys (2016)

#217

Post by Dylan16807 » Fri, Nov 08, 2019, 11:33 PM UTC

spookthesunset wrote: post #170

GauntletWizard wrote: post #155

Earlier quoted context omitted.

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.

Do you not check input from your javascript front-end before you save it? Even if that front-end does its own validation? (No, you see, but there is only one web front-end.... we don't need the backend to validate the input!!!!) In what way is letting the database ensure it isn't getting fed crap any different? Why do developers constantly think it is okay to let unvalidated user input hit their database? Any client…

> In what way is letting the database ensure it isn't getting fed crap any different?

Because one canonical validation layer is generally enough. You can see how having two separate partial validation layers could cause problems, right? And you can't put all the validation in the database, for non-trivial apps.

And "clients" shouldn't be talking to the database, no matter if you have foreign keys or not. That's a totally separate issue.

Re: We do not use foreign keys (2016)

#218

Post by bbrks » Fri, Nov 08, 2019, 11:35 PM UTC

TimTheTinker wrote: post #69

bbrks wrote: post #39

Earlier quoted context omitted.

A foreign key is a hard-constraint that must be enforced. There's no stopping you from storing item IDs in a theoretical shopping cart table without FK constraints though. It's just that the onus is on your application to provide the guarantee that the item exists. This becomes a little clearer if your item IDs are not simply auto-incrementing IDs (which have their own challenges in a large distributed system), but i…

If you have a need for a ID that is unique but otherwise meaningless in a distributed system, feeding a concatenated node ID, timestamp (to ms), and looping transaction counter (mod a sufficiently large number) into a secure hash like SHA-256 should take care of it.

Sure, that satisfies the uniqueness aspect if that's all you're looking for in an ID. But it doesn't satisfy the predictability aspect.

You can't know what the ID will be ahead-of writing the item to the database, nor store the item in a shopping cart without retrieving the item via a different piece of information (which is pretty likely to be SKU in this case)

Re: We do not use foreign keys (2016)

#219

Post by yellowapple » Fri, Nov 08, 2019, 11:39 PM UTC

Hamuko wrote: post #151

zabzonk wrote: post #114

Earlier quoted context omitted.

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

I'm not sure if I understand.

If you're finalizing the invoice, you're hopefully doing something like this, right?

    UPDATE invoices SET
        final = TRUE,
        invoice_number = @InvoiceNumber
    WHERE id = @InvoiceId;

(Where @InvoiceNumber is some variable the application's substituting into the query)

If so, then the problem you present should never happen (unless the DB doesn't do atomic updates by default, but wrapping the update in a transaction should provide the necessary guarantees to prevent the problem from happening; if your DB doesn't support transactions, then you really should be switching to a different DB yesterday).

If that somehow could happen, though (i.e. you don't trust the application to be bug-free, which is a reasonable attitude), a CHECK constraint (as others have pointed out) would make the database enforce that:

    ALTER TABLE invoices
    ADD CONSTRAINT final_invoices_have_invoice_number
    CHECK (final = FALSE OR invoice_number IS NOT NULL);

Of course, I'd also be wondering why an invoice would ever exist without an invoice number (it's easy enough to just make the invoice number NOT NULL - or, better yet, make it the primary key), but hey, if that's the business requirement, then that's the business requirement.

Re: We do not use foreign keys (2016)

#220

Post by brlewis » Fri, Nov 08, 2019, 11:46 PM UTC

papln wrote: post #201

brlewis wrote: post #86

Earlier quoted context omitted.

If the author is thinking of that kind of situation, he's right that ON DELETE CASCADE would be inappropriate, but he's also incredibly wrong to not want a foreign key enforcing that relationship. Deleting a user associated with invoices that should not be deleted is a big no-no too. A foreign key would not let the user be deleted until after the associated invoices are deleted. There are plenty of other situations w…

the problem with ON DELETE CASCADE isn't the CASCADE, it's the DELETE. Almost always, you want to to mark an entity disbled, not really DELETE it. If you want to DELETE it (for GDPR?) you should have something in place to fail your delete unless you've properly defined how to clean up danglig keys. (Perhaps what you need to do is delete the non-primary-key fields containing user data, but keep the row for relational…

> It's not for deleting all your friends when your account is deleted.

Very nice summary up until that point. But in a well normalized db the friends relationship (many-to-many) would be its own table. Friend relationships with the deleted user would be deleted, as they should be. Friends would not. (You still have time to edit for a better example.)