Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: Do you use foreign keys in relational databases?

news.ycombinator.com

Post reply on HN
Print view View original

41–50 of 251 posts

Re: Ask HN: Do you use foreign keys in relational databases?

#41

Post by beachy »

silisili wrote: post #24

Yes. I use FK constraints and cascades. Experience has taught me doing these things in the application layer is very lengthy to get right, often error prone, and rarely as fast.

Even if it was easy to get things right at the application layer, in a legacy system (and today's modern hotness is tomorrow's legacy system), the database is a constant.

Entire generations of application may rise and fall. New languages, frameworks, developers all lead to rot over time. Heck, some legacy projects the application code is incomplete or lost.

But the database doesn't rot. Show me a database that is 20 years old, and it is as fresh as the day it was created. If it has FK constraints, it's even healthier.

That means that the more value is embedded in the database - FK and of course many more constraints - that value will live for decades.

Re: Ask HN: Do you use foreign keys in relational databases?

#42

Post by floppydisc »

Yes, but it definitely depends on the model I'm putting into the database.

Specifically I hadn't thought through an Order model and the OrderLines ended up being dependent directly on the product meaning through an FK. The orders wouldn't "settle" once they had been completed, since the product could be updated and change the values of the orderline and order. Dumb dumb. It was one of the cases where denormalizing data, very much, makes sense

Re: Ask HN: Do you use foreign keys in relational databases?

#43

Post by charcircuit »

devmor wrote: post #19

I use FKs because I have built my career on refactoring old software. I have seen over and over firsthand the kinds of data integrity problems that come from leaving the decision to the business software and those who meddle asynchronously with data. You can always rewrite software. Rewriting bad data is not only difficult but often impossible.

>You can always rewrite software. Rewriting bad data is not only difficult but often impossible.

Isn't that an argument against FKs? It's easier to rewrite the software to handle FKs than to deal with trying to setup FKs with bad data since it's difficult to fix.

Re: Ask HN: Do you use foreign keys in relational databases?

#44

Post by iforgotpassword »

I use FKs and have migrated/updated the scheme multiple times over the years. If something goes wrong during migration that means I don't understand my own scheme properly. Getting rid of FKs for that reason feels like disabling all compiler warnings, or wrapping every single method in java in a try-catch block with an empty catch part.

Re: Ask HN: Do you use foreign keys in relational databases?

#46

Post by dmak »

charcircuit wrote: post #43
devmor wrote: post #19

I use FKs because I have built my career on refactoring old software. I have seen over and over firsthand the kinds of data integrity problems that come from leaving the decision to the business software and those who meddle asynchronously with data. You can always rewrite software. Rewriting bad data is not only difficult but often impossible.

>You can always rewrite software. Rewriting bad data is not only difficult but often impossible. Isn't that an argument against FKs? It's easier to rewrite the software to handle FKs than to deal with trying to setup FKs with bad data since it's difficult to fix.

Data often outlives the teams that input it. Often the case is, you can't recapture that domain knowledge to rebuild the missing data.

Re: Ask HN: Do you use foreign keys in relational databases?

#47

Post by Philip-J-Fry »

He's talking about FK constraints right?

We do not use them for write performance concerns.

There's plenty of nice features of the DB we're not allowed to use under the excuse of "performance". But I'm told this by people who live and breathe SQL, so I trust them and I hope they have evidence to back it up. Because a lot of these features we're not allowed to use would make our lives 100x easier if we could!

Re: Ask HN: Do you use foreign keys in relational databases?

#49

Post by pharmakom »

mtts wrote: post #31

Fear of RDBMSes is quite common. I used to suffer from it too. It’s just so annoying to have to switch your brain to a different programming paradigm every time you need to do something with the database that you start to make up all sorts of excuses as to why it’s really just better to “do it in the code”. Your coworkers argument about FKs making data migrations difficult is one of them. Another classic is the “join…

How about when the ID in a FK column has been generated outside the RDBMS but the target of the ID has not been written yet?

Re: Ask HN: Do you use foreign keys in relational databases?

#50

Post by CraigJPerry »

There’s a pithy quote by someone famous in DB circles who said something like “normalise until it hurts, de-normalise until it’s fast enough” - I’m vague on the exact words used but that’s the gist of it.

I’ve never come across a scenario yet where this wasn’t sound advice.

I tend to lean heavily on my DB as well. E.g. I tend to push all state down to the DB and out of the application. I work in environments where it’s common for developers to want to disable FK constraints, and i temporarily do sometimes during specific bulk operations during releases for example. The usual reasons others will suggest relaxing FK constraints permanently will be due to the need for audit logs or soft deletes but i have patterns for these too.

Foreign key constraints are pretty awesome, all databases I’ve worked in so far have escape hatches for when they hurt too much.

Post reply on HN