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

161–170 of 251 posts

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

#161

Post by CuriouslyC »

10x-dev wrote: post #128
sonthonax wrote: post #108

Earlier quoted context omitted.

> Another classic is the “joins are slow” argument The only person I knew who died on that hill would insist on doing two queries to the database, and then would insist on doing a client side cartesian join.

Are joins in a 5NF database now as fast as querying a denormalized database?

Seq scans will be faster in a normal form database, if you're seq scanning then joining other tables on an index it might be faster. Otherwise the denormalized table will probably be faster.

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

#162

Post by CuriouslyC »

AdamN wrote: post #126
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.

ORM is a very valuable tool and should be aggressively used. One can always step down to SQL as needed but otherwise, the ORM logic is easier to write and maintain.

ORM logic is not easier to write and maintain unless you're bad at SQL and writing your queries without any tool support.

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

#163

Post by dspillett »

jrumbut wrote: post #36

Foreign key constraints are easy to remove if they become a problem but almost impossible to add once data integrity problems arise (and I've never seen them not arise in projects without FK constraints). The small number of people with high enough scale that they can't use them know who they are, the rest of us need to think carefully when performing database migrations and reason out the order of operations require…

> sounds like a good idea anyway?

Heck, yes. If you are butting against constraints then you may have misunderstood the problem.

I don't mind people turning constrains off for a mass migration under the following conditions:

• This is not production, or if it is production you are in a maintenance window during which you have exclusive access to this DB

• The data you are piling in will be verified against the constraints once the job is complete (no turning things back on with “WITH NOCHECK”), and you have a rollback plan for if that fails (“restore from backup take before the maintenance window” might be acceptable, if that can be done in the timeframe of the maintenance window and you are happy taking questions from your team/clients/management if this means planned updates have to be completely postponed).

• You can explain why not getting the data to be modified in an order that allows the constraints to be kept on through the process would be significantly more complicated (simplifying migration code is a valid reason for temporarily turning off constraints if it makes maintaining the relevant code less error prone) or significantly slower (sometimes doing it most right unavoidably takes more time) or both.

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

#164

Post by Spivak »

pif wrote: post #143

I agree with your colleague, and I insist on pushing my car everywhere because I fear gas as it is flammable. In other words, the world is full of idiots; and any time I start forgetting about it, I read something like your post and I get a wake-up call. What does R stand for in RDBMS is you don't use foreign keys and joins? Please, keep using your FKs, stay safe and don't mingle too much with idiots.

Eh, there are a lot of people who don't like using FK constraints, calling them all idiots is just bad faith and ignores the reasons they did it. Just because you can enforce a constraint at a specific layer doesn't mean you have to. DB people love shoving all sorts of application logic into the DB and there are good arguments to do it as well as downsides. App people sometimes prefer to do everything in the app and just let the DB be a dumb data store and there are good arguments for that too. But it depends isn't a hot take.

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

#165

Post by pc86 »

Antshockey wrote: post #105
fifticon wrote: post #86

Earlier quoted context omitted.

I concur with these. I see colleagues who use EF as 'black magic', who postpone or fear looking into what is happening under the hood. Because they lack insight into what it really does, they regularly cause horrendous queries to happen. My pet peeve with EF LINQ is, that your c# is not really c#, so you may write queries that compile silently, but fail to execute on runtime because the C# cannot be translated to SQL…

Funnily, one of my pet peeves is people worrying about the SQL Generated from EF Linq. If you care that much about it I think you should just be writing the SQL by hand.

I might even go a step farther - you shouldn't care. It's the equivalent of caring whether or not your generated HTML or compiled IL or Assembly "looks nice."

If the SQL is performant and it returns the expected data, that is good enough for 99.9% of cases.

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

#166

Post by 10x-dev »

viraptor wrote: post #137
10x-dev wrote: post #128

Earlier quoted context omitted.

Are joins in a 5NF database now as fast as querying a denormalized database?

Maybe I'm missing some context, but isn't that true by definition even if the db does nothing special? You either spend time sending N queries and waiting for responses, or join and use one query. Given actually matching scenarios for both, the one with less communication overhead wins.

In a normalized database that's true, but in a denormalized database, by definition, you get a third option, which is to have tables with redundant data that can be returned in a single query (as if it were pre-joined, I suppose).

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

#167

Post by pc86 »

swasheck wrote: post #151
AdamN wrote: post #126

Earlier quoted context omitted.

ORM is a very valuable tool and should be aggressively used. One can always step down to SQL as needed but otherwise, the ORM logic is easier to write and maintain.

the all-or-nothing approach is prevalent in both camps. i’ve worked in places where a straightforward optimization could not be implemented because it would require the developers to break from the orm-only standard they’d set. i’ve also worked places where orm were held as such anathema that any orm proposal was dismissed out of hand without any sort of discussion.

As is almost always the case, the middle ground is better. In a few of the larger .NET projects I've worked on we would use EF until it became too much of a pain (or the business rules made the query unmanageable) and then someone would eventually spend a few days transitioning the query into a stored procedure + SQL functions, updating tests, etc. The biggest complaint I ever got from that was that it was impossible to tell what was going to be LINQ and what was going to be a stored procedure. While not an insignificant concern, it certainly beats "oh we can't optimize that 20-second long query because then we'd have to drop below the ORM and 'we don't do that here'" or "no you can't use any ORM even though it objectively eliminates a lot of boilerplate work."

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

#168

Post by Topgamer7 »

tommica wrote: post #16
Topgamer7 wrote: post #11

Is there a way to have foreign keys without the index and integrity constraint. I would love to be able to keep the relational mapping at the db layer. (I almost always actually want the integrity and indedx, but I'm just curious if its possible)

Isn't that just storing the value in a normal column?

What I'm specifically looking for is a foreign key relation so that you could for example still generate a chart of relations. For example an audit log you wouldn't want the consistency check, but it would still be nice to know it links to X tables.

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

#169

Post by axylos »

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…

> Your coworkers argument about FKs making data migrations difficult is one of them.

Got any arguments to back up this bald assertion?

In particular, I'd love to hear more about how to manage schema migrations on large tables with FK's without incurring lengthy locks or downtime.

Betting the answer is going to involve some variation on "well, don't do that" which is when I'll rest my case.

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

#170

Post by FlyingSnake »

Spivak wrote: post #164
pif wrote: post #143

I agree with your colleague, and I insist on pushing my car everywhere because I fear gas as it is flammable. In other words, the world is full of idiots; and any time I start forgetting about it, I read something like your post and I get a wake-up call. What does R stand for in RDBMS is you don't use foreign keys and joins? Please, keep using your FKs, stay safe and don't mingle too much with idiots.

Eh, there are a lot of people who don't like using FK constraints, calling them all idiots is just bad faith and ignores the reasons they did it. Just because you can enforce a constraint at a specific layer doesn't mean you have to. DB people love shoving all sorts of application logic into the DB and there are good arguments to do it as well as downsides. App people sometimes prefer to do everything in the app and…

If you're using an RDBMS and not using FK or other relational constraints, how do you plan to maintain referential integrity?
Post reply on HN