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

81–90 of 251 posts

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

#81

Post by kureikain »

I used to scare of it thinking why do I need it? everytime I delete sth it annoying me. I read blog post of GIthub not using FK key, and try to convince myself that FK isn't worth it.

One day, I decided let try this out. And turning out it wasy easiser than I think. When delete data just make sure to use cascade or ensure its associated data is deleted, which make sense.

Once I embrace it, FK now becomes my friend to enter data consistency, I cannot imagine how did I live without it before.

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

#82

Post by sverhagen »

Absolutely "yes" on foreign key constraints.

There _is_ a scale, though. I have worked in enterprises where the database is the "contract" and where the business logic is implemented in the database, with triggers and all sorts of constraints and what not, in addition to foreign key constraints. But I now mostly work in service- or micro-service-oriented places where the "contract" is a REST(-ish) API, business logic is implemented in Java, and the database is exclusively owned by that one code-base, where not all business logic is replicated from the Java code into the database (still foreign keys, though!)

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

#83

Post by mccorrinall »

kureikain wrote: post #81

I used to scare of it thinking why do I need it? everytime I delete sth it annoying me. I read blog post of GIthub not using FK key, and try to convince myself that FK isn't worth it. One day, I decided let try this out. And turning out it wasy easiser than I think. When delete data just make sure to use cascade or ensure its associated data is deleted, which make sense. Once I embrace it, FK now becomes my friend to…

It’s can become pretty annoying if you want to alter your database schema.

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

#84

Post by tailspin2019 »

sverhagen wrote: post #80
tailspin2019 wrote: post #67

Earlier quoted context omitted.

> 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 tha…

I definitely see that, and ORMs (particularly older ones) have historically made it easy to shoot yourself in the foot.

But, everything is an abstraction, and I tend to think that if you use any abstraction, you need to have at least a little bit of knowledge about what’s happening in the layer beneath it.

So using an ORM will not be an optimal experience if you don’t know how the underlying RDBMS works.

And effectively using an RDBMS directly still requires a bit of knowledge about the layer below that level of abstraction too (eg how underlying query optimisation works etc).

It’s possible to implement both incorrectly and get bad results and the opposite is true too

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

#85

Post by Cthulhu_ »

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 had this when importing test data; I found it acceptable (since it was just in development) to temporarily turn off FK checking.

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

#86

Post by fifticon »

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…

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.

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

#89

Post by sergiosgc »

PaulHoule wrote: post #3

It is an old debate. You can get into awkward situations when you save and restore tables, everything has to happen in the right order and there's always the fear of some circular situation. I'm remembering the time I was working at a place that had a huge number of Microsoft Access, Microsoft SQL Server and mysql databases and I was the first person they'd hired who knew how to do joins and they thought it was prett…

That's not how you migrate. You disable the foreign keys, load the data, then enable them (which checks everything is ok).

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

#90

Post by forinti »

You need FKs to ensure you don't delete data that's still needed. If your data is important, you have to use FKs.

You also need to index your FKs so that the database does not have to do a full table search before you can delete a row. This is often overlooked.

Your friend can do his data migrations without FKs and create them afterwards. This is quite a common procedure.

Post reply on HN