Do you really need foreign keys? • Hacker News Board

Re: Do you really need foreign keys?

#161

Post by dasil003 » Thu, Dec 21, 2023, 11:57 PM UTC

pmontra wrote: post #145

dasil003 wrote: post #120

Earlier quoted context omitted.

Interesting that this whole thread makes no distinction between read and write access, as those are dramatically different use cases. Read access is by far the more necessary, and is usually solved relative easily by saving snapshots to a data warehouse. This is no panacea as data can still easily be misinterpreted or replicated and used out of context in violation of expected production data lifecycle by the team th…

The no foreign keys advice tastes of 2005/2010 when the lack of support in some frameworks (Rails included) prompted developers to dismiss them as unnecessary. This piece of advice at least quantifies the terms of the tradeoff. Regarding the correct "saving snapshots to a data warehouse", if there are one million web apps in the world, how many of them have the scale to noticeably benefit from either doing without fo…

> Regarding the correct "saving snapshots to a data warehouse", if there are one million web apps in the world, how many of them have the scale to noticeably benefit from either doing without foreign keys or from a data warehouse?

Yes I agree. This advice was assuming you have scale that necessitates multiple services, at which point you'll want to be able to query against data from multiple sources. I'm with you that the vast majority of teams (finger in the air: less than 20 full-time engineers working on a typical web app) are probably better off with a monolith and single database. At this point a read replica is a low effort way to safely provide access to a wide range of stakeholders.

Re: Do you really need foreign keys?

#162

Post by butlerm » Fri, Dec 22, 2023, 12:21 AM UTC

iLoveOncall wrote: post #25

lrobinovitch wrote: post #14

This github issue is often linked when this topic is discussed: https://github.com/github/gh-ost/issues/331 > Personally, it took me quite a few years to make up my mind about whether foreign keys are good or evil, and for the past 3 years I'm in the unchanging strong opinion that foreign keys should not be used. Main reasons are: > * FKs are in your way to shard your database. Your app is accustomed to rely on FK to…

> FKs are a performance impact. The fact they require indexes is likely fine, since those indexes are needed anyhow. But the lookup made for each insert/delete is an overhead. This is not a valid argument at all and I'm concerned anyone would think it is. If you have a foreign key, it means you have a dependency that needs to be updated or deleted. If that's the case, you will have an overhead anyway, the only questi…

> If that's the case, you will have an overhead anyway, the only question being whether it's at the DB level or at the application level.

Inserts and updates do not require referential integrity checking if you know that the reference in question is valid in advance. Common cases are references to rows you create in the same transaction or rows you know will not be deleted.

If you actually want to delete something that may be referred to elsewhere then checking is appropriate of course, and in many applications such checking is necessary in advance so you have some idea whether something can be deleted (and if not why not). That type of check may not be race free of course, hence "some idea".

Re: Do you really need foreign keys?

#163

Post by carcabob » Fri, Dec 22, 2023, 12:46 AM UTC

Novice Question: How can you have a relational database at all without foreign keys?

In other words, if your tables aren't referencing other tables, how do you perform JOINS for your queries? From my limited experience, they seem pretty essential for most databases. Or is the article just talking about foreign key CONSTRAINTS and cascading UPDATES and DELETES, rather than literally talking about the values stored in foreign key columns?

If anyone can explain, I would greatly appreciate it.

Re: Do you really need foreign keys?

#164

Post by sseagull » Fri, Dec 22, 2023, 2:04 AM UTC

carcabob wrote: post #163

Novice Question: How can you have a relational database at all without foreign keys? In other words, if your tables aren't referencing other tables, how do you perform JOINS for your queries? From my limited experience, they seem pretty essential for most databases. Or is the article just talking about foreign key CONSTRAINTS and cascading UPDATES and DELETES, rather than literally talking about the values stored in…

Foreign keys are really just constraints - the row referred to by the FK must exist, and the database will ensure that. That is the basic idea. Cascading, etc are just useful features on top.

While FKs and JOINs often go hand in hand, JOINs actually do not require foreign keys or any other constraint. You can join any table and column that you want regardless of FK or index or whatever (although you have to be careful about performance).

Re: Do you really need foreign keys?

#165

Post by redandblack » Fri, Dec 22, 2023, 2:36 AM UTC

Is 3rd normal not something used in design? Obviously, I have dropped off more than 20 years but distinctly remember that design saving our product evolution over 10 years or more - really a working early 90's designed system in 00's

Re: Do you really need foreign keys?

#166

Post by whynotmaybe » Fri, Dec 22, 2023, 4:24 AM UTC

BenoitP wrote: post #49

My rule of thumb has been: enable them strictly in DEV and INT environments, disable in PROD. They can catch schema discrepancies, but can impede ingestion rates. Also some referential errors are sort of ok in PROD, as long as it's only about not dropping user data; which can be dealt with later on (INT gets reset with PROD user data from a backup each week, it also helps in the restore plan, fk are enabled, errors a…

Whatever you do, always have your dev/test environment identical to production.

Have a load balancer or replication in prod? You must have the same when you test.

Otherwise, you will have a bad time m'kay.

And don't get me started about removing fk constraint, anybody doing that on purpose is either very, very smart, either very, very ignorant/inexperienced.

Re: Do you really need foreign keys?

#167

Post by pmontra » Fri, Dec 22, 2023, 7:00 AM UTC

ttfkam wrote: post #148

pmontra wrote: post #145

Earlier quoted context omitted.

The no foreign keys advice tastes of 2005/2010 when the lack of support in some frameworks (Rails included) prompted developers to dismiss them as unnecessary. This piece of advice at least quantifies the terms of the tradeoff. Regarding the correct "saving snapshots to a data warehouse", if there are one million web apps in the world, how many of them have the scale to noticeably benefit from either doing without fo…

If I recall correctly, "no foreign keys" hit its stride back in early PHP days when MySQL didn't support them properly. Rather than cop to the fact that MySQL just implemented them badly, MySQL AB went on a dev PR run telling folks that foreign keys weren't actually useful and just slowed a system down. Once MySQL implemented them less horribly, the PR push finally started to die down. I will never forgive them for t…

Nobody with a Computer Science or especially a Software Engineering degree should have felt for it but I know a number of good developers with a more varied background. Some of them inevitably became team leaders etc. By varied backgrounds I mean Philosophy, Agricultural Sciences, Graphic Design. Some of them know very well how a database work, some admit to never have learned SQL, go figure all the theory and the rationale behind some technologies. Give them some blog posts that make the job done without foreign keys and they won't even know what a foreign key is until it's 2015 and people started to move to PostgreSQL.

Re: Do you really need foreign keys?

#168

Post by ww520 » Fri, Dec 22, 2023, 8:11 AM UTC

arp242 wrote: post #68

ww520 wrote: post #41

The performance gain by dropping foreign keys doesn’t hold water. You still have to do the referential checks in application or in the ORM code. Unless you meant dropping referential checks.

Here' the common scenario: obj = new_object() obj.col1 = get_value_from_somewhere() obj.user_id = get_logged_in_user_id() obj.insert() Or: # Find correct ID. obj.some_id = run_query("select some_id from tbl where x=?", param) Lots of variation on that, but the user_id and some_id here are pretty much guaranteed to be accurate when implemented correctly. The biggest potential issue might be race conditions with delete…

What you picked are the happy paths.

Here's the common scenario:

  deals = sql("select * from current_deals ...");
  // Show the user the deals.
  // User takes his sweet time to ponder over the deals.
  // Meanwhile, the deals are gone.
  order = new_order()
  order.deal_id = deals[5].id
  order.insert()
  // User calls in to complain never receiving the deal.

  // Ok, need to verify the deal still exists.
  sql("select deal from current_deals where id = ? for update", deals[5].id)
  ... insert ...
  sql("commit")

That's second select..for update is referential checking in code.

Re: Do you really need foreign keys?

#169

Post by boobsbr » Fri, Dec 22, 2023, 9:23 AM UTC

Scubabear68 wrote: post #124

I do consulting for a lot of different companies. The older ones who did not religiously use PK/FK constraints have databases that are nightmares to maintain. The data will start to rot surprisingly quickly, devs will react by writing weird code to compensate, and your life will not be fun.

I worked in different banks for the better part of a decade, lots of internal applications, each with their own DB.

They all had PK/FK/unique/check constraints, and they were still a clusterfuck to understand and maintain. Lots of outright stupid and dangerous code to work around the issues.

Still, if there were no constraints, it would have been even worse, as some 'intelligent' people temporarily disabled constraints to update data, resulting in data integrity hell.

Re: Do you really need foreign keys?

#170

Post by GoblinSlayer » Fri, Dec 22, 2023, 10:24 AM UTC

ch4s3 wrote: post #104

epicfeedback wrote: post #88

Earlier quoted context omitted.

They are you. You is they. It’s one company with one goal — keep companying. The idea that ever “department” should access every other department’s data through some bespoke interface that the latter department maintains might work at some corporate behemoth, but at almost all other scales is absurd.

This would be a total waste of effort when you need to be building a product and iterating. I hate articles like this because they do a poor job contextualizing the tradeoffs and when it might be appropriate to do the weird exceptional thing. IMHO if you have a performance critical case when foreign keys are in the way, load THAT data into an in memory DB on a recurring basis and server time sensitive requests from t…

Foreign keys are slow on delete, not on read. If you have a popular table, say, users, and all other tables refer to it, then deleting a user locks the database for time proportional to the number of foreign keys - good old linear scaling.