Ask HN: Do you use foreign keys in relational databases? • Hacker News Board

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

#151

Post by swasheck » Tue, Sep 06, 2022, 1:15 PM UTC

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.

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.

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

#152

Post by sethammons » Tue, Sep 06, 2022, 1:16 PM UTC

By default, yes, FKs and joins. However, I tend to work on high scale systems generally backed (primarily) by mysql. To scale out (tbs of data, hundreds of tables, millions of users, billions of daily interactions), we've had to remove some FKs, avoid db triggers, use caching heavily (including piggy backing requests where you don't serve an identical request from the db, you wait for the response and returned the memoized version). When you do choose to remove a FK, the need doesn't go away, you just move it. Now you have to look into data sync and out-of-band integrity checks and you have to have a plan on what to do when you go looking for data that is no longer there.

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

#153

Post by zzzeek » Tue, Sep 06, 2022, 1:17 PM UTC

> He rather have a smooth data migration process than having an unexpected error and abort the whole operation that the migration is only a small part of.

that's some take, because if that "unexpected error" is not reported because FK integrity was turned off, that means the data containing integrity errors goes right in. Now your database is corrupt. Dealing with a DB where random rows here and there are not conforming to implied-only constraints is zero fun. in my own experience, things like the main page of the production site is a 500 error, with a stack trace deep into some logic nobody has looked at in two years, run the identical code on staging, works fine. Fun stuff! Seems like an odd choice to let errors like that stream right into your production DB without checking.

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

#154

Post by hu3 » Tue, Sep 06, 2022, 1:21 PM UTC

autarch wrote: post #145

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.

The "R" stands for "relations", as in "relations", which is a mathematical concept. SQL calls a "relation" a "table". The "relational" is RDBMS has nothing to do with relationships. But I still agree that OP's colleague is an idiot.

That might have been how it started (https://www.ibm.com/ibm/history/ibm100/us/en/icons/reldb/).

But it's definitely not what it means for the great majority of contemporary contexts.

Relations in modern RDBMS are usually aliases to foreign keys unless otherwise specified.

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

#155

Post by bartread » Tue, Sep 06, 2022, 1:21 PM UTC

My other beef with no foreign keys is that it makes a database a lot harder to understand.

There are any number of tools that will generate me a pretty and useful database schema diagram if I point them at a relational database. This is incredibly handy when you're new to a database and need to figure out which tables to query and update, and which (gasp) sprocs you need to call. I've been on projects where people have been poking around in the dark, and then a good database diagram has saved us days or weeks of effort trying to figure out how to make something work. As I say, there are plenty of tools capable of generating one of these in seconds or minutes[0].

However, if you don't have foreign keys, the utility of such a tool is severely diminished because you just get a big pile of nodes representing tables clustered at the top or bottom of your diagram (depending on exactly which layout algorithm is being used and how it's been configured).

[0] Many years ago I and three colleagues built one of these: Redgate's SQL Dependency Tracker (https://www.red-gate.com/products/sql-development/sql-depend...). It was pretty neat because you could build a diagram spanning databases, or even linked servers, and unlike most other tools at the time it could handle thousands and thousands of database objects, but the product name doesn't really help get across that it's fundamentally a diagramming tool. I built the dependency engine, the graph, and radar views. We used yFiles from yWorks for the graph layout calculations, with a bit of extra hackery, but I seem to remember yFiles lost compatibility with a newer version of .NET at some point so (or something along those lines) so RG ended up swapping it out for something else.

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

#156

Post by jonnycomputer » Tue, Sep 06, 2022, 1:21 PM UTC

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.

Mathematically a relation is a set of tuples; which is exactly what a table is.

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

#157

Post by bvrmn » Tue, Sep 06, 2022, 1:23 PM UTC

mattewong wrote: post #53

There is only upside to using foreign keys. When enabled, the benefits are obvious, so I won't discuss, and as for the drawbacks, the only drawback is performance. But if that's a concern, then guess what, on most databases, you can disable foreign keys. Well, one might ask, what is the point of having foreign keys if they are disabled? And the answer is, there are several benefits. Here are a couple: 1. foreign keys…

I have an anecdote from my experience. We had an unpleasant debug week in search of an cause of slow insertion query in PG to a particular table. 15 FKs to the table were the reason of a bottleneck.

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

#158

Post by JohnDeHope » Tue, Sep 06, 2022, 1:27 PM UTC

I do app databases for a living, so I thought I'd dump my 2 cents here. I am very much not on a high horse about it. I'd be perfectly happy to use FK if somebody felt strongly about it. I myself have never found them helpful. My databases are small enough and I write all the SQL access code by hand, so nobody is touching the data except me, and the SQL that I write myself. Things being half-deleted isn't a problem. In fact I can't ever remember where that has happened. I have lots of other problems, but that's not one of them. PKs, indexes, views, etc. I use all of that stuff, but not so much FK. YMMV. Prohibiting joins is throwing the baby out with the bathwater.

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

#159

Post by wahnfrieden » Tue, Sep 06, 2022, 1:29 PM UTC

samatman wrote: post #132

Do... you not turn the foreign key constraints off during migration? In SQLite it's a pragma, in Postgres you turn triggers off, those are the ones I've personally done but surely any relational database has this ability for this specific reason ?

best to use tools for this such as https://github.com/fabianlindfors/reshape or gh-ost for mysql

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

#160

Post by jongjong » Tue, Sep 06, 2022, 1:30 PM UTC

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.

I think the author is talking about 'foreign key constraints' - You could have foreign keys without enforcing a constraint.

Personally, I don't use foreign key constraints because:

1. It makes schema migrations and other data-management operations more difficult.

2. On insertion, the database needs to perform an additional check to verify that the record exists at the foreign key; this carries a performance cost; IMO, this is something which should be enforced at the application layer anyway.

3. It makes it more difficult to scale the database later because you can't separate tables onto 2 different hosts if one table references another using a foreign key.

BTW, about #3, the same argument can be made against using table joins. Once you start using foreign keys or table joins, you will be forced to run those two tables on the same host in the foreseeable future; it's very difficult, error-prone and time consuming to migrate away from such architecture if you have a lot of data in a live environment. Personally I prefer to design all my tables and front end applications to not rely on foreign keys or table joins. There is a good reason why databases which are focused on scalability (like MongoDB) do not support foreign keys or joins (or at least they try to avoid them).

I prefer to assemble data on the front end as much as possible because it allows my REST API calls to be granular; each one only refers to a single kind of resource; this helps to simplify caching and real-time updates; it also uses fewer resources on the server side and I find that it makes the front-end code more maintainable. Also, I like to design my front ends to mirror the natural separation of resources within the database. When the user wants to open up a related record, they need to click on a link (the foreign key ID/UUID is used to construct the link to the related resource); this loads up the other record as a separate step. This creates a very smooth (and fast) user experience - I also like it because this approach does not overload the user with information; collections of items don't show much details, on the other hand, individual resources may show a lot of detail.

The real reason people use joins is because they want to pack a lot of details onto the user's screen when they are looking at a list view... Sometimes the reason why they want to do that is because they didn't design their tables correctly; maybe the tables which they use to generate list views don't contain enough columns/detail to be useful on their own so they feel forced to do joins. I find that drawing ER diagrams helps a lot with that. It's very important to get the cardinality of relationships between the different tables exactly right. Also, I find it very helpful to represent any many-to-many relation between two tables as a distinct table.