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

71–80 of 251 posts

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

#71

Post by GoblinSlayer »

pharmakom wrote: post #49
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?

I assume the target is externally generated too, thus can be legitimately absent.

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

#72

Post by masklinn »

pmontra wrote: post #25
belltaco wrote: post #2

Why not just turn off foreign key checks during migrations and after validating the data, turn them back on? SQL Server lets you do this.

But you could get duplicate key errors when turning fk on. A customer of mine uses UUIDs as keys. That makes almost impossible to get duplicate keys and removes any problem with moving data from one db to another. Reading UUIDs is a minor pain though.

> But you could get duplicate key errors when turning fk on.

Why? Fks don’t require unicity. Or do you mean that you target non-unique columns? That sounds… horrendous.

But regardless sounds like something you’d want to resolve before declaring the migration finished.

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

#74

Post by bawolff »

connormcd wrote: post #58
Philip-J-Fry wrote: post #47

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!

If you've got a bit of time to spare, here's a 30min walk through of physical design in the database. https://youtu.be/x0P4zAptTiA In this talk, we start at 10,000 transactions per second, and just by altering the design we get to 20,000 transactions per second... all on a 5 year old laptop. And at no time did we ditch any constraints (primary or foreign). The claim that you can't get performance with constraints on…

I mean, was that 20,000 transactions that are likely to have lock contention related to foreign key constraints? Because otherwise you are measuring the wrong thing.

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

#75

Post by moonchrome »

Semaphor wrote: post #57
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…

> Much better than ORMs I recently migrated to EntityFramework Core (from the non-core version) and I’m actually impressed. Most SQL is pretty much what I’d write by hand. Now granted, if there are complex joins, subqueries and stuff, I don’t even try wrangling the ORM to somehow give me that output, but still. I feel more comfortable just using EF than I used to.

My main problem with Entity Framework is the magic underneath.

Like simple operation

    x = Ef.Find(xid)
    x.Name = "something"
    y = Ef.Find(xid)
what is y.Name ? Even though you didn't save anything to the database yet ? And the second Find didn't actually refresh from the database ?

Oh and the random bugs where people improperly include related entities but it somehow ends up working because they are automatically added as you're firing off other related queries, until eventually it does not (usually in production only).

It's a really really complex system designed to look simple and pave over important details with "works most of the time" defaults.

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

#77

Post by geraldwhen »

moonchrome wrote: post #75
Semaphor wrote: post #57

Earlier quoted context omitted.

> Much better than ORMs I recently migrated to EntityFramework Core (from the non-core version) and I’m actually impressed. Most SQL is pretty much what I’d write by hand. Now granted, if there are complex joins, subqueries and stuff, I don’t even try wrangling the ORM to somehow give me that output, but still. I feel more comfortable just using EF than I used to.

My main problem with Entity Framework is the magic underneath. Like simple operation x = Ef.Find(xid) x.Name = "something" y = Ef.Find(xid) what is y.Name ? Even though you didn't save anything to the database yet ? And the second Find didn't actually refresh from the database ? Oh and the random bugs where people improperly include related entities but it somehow ends up working because they are automatically added…

This is why I like TypeORM. There is no magic, and every command maps 1:1 with a database operation.

No weird caching, no auto saves. Just an object mapper that you can use when you want and ignore when you need to.

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

#78

Post by mejutoco »

I can relate partly to the problem. Sometimes you want to import a single table from a backup and, because of FK constraints, a very specific order in the tables is required, and can span many tables depending on the complexity of the data.

As an alternative you can use Foreign keys without constraints. This way you get the conveniences of them without the migration problems. You can do this permanently (a bit wilder) or just before an import, and re-enable it later.

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

#80

Post by sverhagen »

tailspin2019 wrote: post #67
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…

> RDBMSes are highly optimized pieces of software > Much better than ORMs These two things are not mutually exclusive though right? It’s entirely possible to have a lightweight and relatively transparent ORM which makes full use of the underlying RDBMS.

Yeah, I was going to say something similar. But ORMs get blamed for obscuring what's going on, to the point that a developer may end up doing some sort of inefficient 1-to-n lookup that would've indeed been much better off as a SQL JOIN.

I use JPA/Hibernate professionally, as a decision maker, but I don't think I'm in either camp entirely. ORMs aren't a magic wand, but they do help you standardize the boilerplate that you'd end up with one way or the other, in most cases.

Post reply on HN