Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Do you really need foreign keys?

shayon.dev

Post reply on HN
Print view View original

11–20 of 179 posts

Re: Do you really need foreign keys?

#11

Post by PeterisP »

cube2222 wrote: post #4

On that note, has anybody figured out a nice way to combine foreign keys and soft deletion (that is, a deleted_at column)? Soft deletion is occasionally useful, but losing foreign keys for it is a big pain. EDIT: got a bunch of responses, thanks! To be clear, the issue I have in mind is e.g. you want to have a foreign key that makes sure the “singular” side of a one-to-many relationship isn’t soft-deleted (on delete…

Just brainstorming - perhaps one way to do that would be a multi-column FK towards the row ID combined with an is_deleted column, which would mean that the FK constraint also enforces updating the related rows if something is deleted.

Re: Do you really need foreign keys?

#12

Post by endisneigh »

You don’t need foreign keys, no. But you do need referential integrity and foreign keys implemented and enforced by the database is usually the easiest.

If you’re disciplined it’s not difficult to implement yourself in the application, but the challenge is if folks can access the database directly. If so, good luck, since it’s inevitable that they end up making modifications and break integrity.

If one insists on not having foreign keys (and there are decent reasons to not have them), I would really really suggest not allowing direct access to the database and enforce whatever application be the main or only client to the database.

Re: Do you really need foreign keys?

#14

Post by lrobinovitch »

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 maintain integrity, instead of doing it on its own. It may even rely on FK to cascade deletes (shudder). When eventually you want to shard or extract data out, you need to change & test the app to an unknown extent.

> * 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.

> * FKs don't work well with online schema migrations.

Re: Do you really need foreign keys?

#15

Post by wombatpm »

zer00eyz wrote: post #5

Ahh the old days of LAMP when the M was mysql and foreign keys were just a dream... When you reduce complexity and take off the safeguards things get faster! Cock that foot gun and hope that it doesn't go off! Can you do what the author suggests. You sure can and we did it for a long time with MYSQL. Should you? It depends on your team, how in tune they are with working with databases, sql etc...

If you are willing to forego 3rd normal form and just duplicate data like mad, and have a million column like a giant excel sheet, then you really don’t need foreign keys.

Re: Do you really need foreign keys?

#16

Post by dspillett »

stop50 wrote: post #10
cube2222 wrote: post #4

On that note, has anybody figured out a nice way to combine foreign keys and soft deletion (that is, a deleted_at column)? Soft deletion is occasionally useful, but losing foreign keys for it is a big pain. EDIT: got a bunch of responses, thanks! To be clear, the issue I have in mind is e.g. you want to have a foreign key that makes sure the “singular” side of a one-to-many relationship isn’t soft-deleted (on delete…

How are foreign keys and deleted_at mutual exclusive?

I assumed cube2222 meant where soft-delete wasn't universal: so you might really delete the parent row but want to soft-delete the child rows. Though I would suggest this is a bad idea: if you want to soft-delete child rows you should enable it on parent rows too.

Re: Do you really need foreign keys?

#17

Post by x86_64Ubuntu »

KeyBoardG wrote: post #2

Unless your project is small and for learning, or won't be around for very long, Yes. You absolutely do or will end up in a technical debt world of hurt. I have worked on systems without them that are 15+ years old and the vast, vast majority of fixes and refactoring were self inflicted wounds like this. We added FK's and indexes where necessary and wound up faster and safer.

That sounds awful. My first instinct would be to see where an FK constraint would fail, and then ask the business what it should be. There is no guarantee that every business case from way back needed an FK, or that anyone in the business knows which FK value should be there. It sounds absolutely miserable.

Re: Do you really need foreign keys?

#18

Post by shayonj »

endisneigh wrote: post #12

You don’t need foreign keys, no. But you do need referential integrity and foreign keys implemented and enforced by the database is usually the easiest. If you’re disciplined it’s not difficult to implement yourself in the application, but the challenge is if folks can access the database directly. If so, good luck, since it’s inevitable that they end up making modifications and break integrity. If one insists on not…

That is great and I noticed it’s something I have always assumed should be the case - no indirect access to the database outside your application / ORM. Because then you open a can of worms.

Re: Do you really need foreign keys?

#19

Post by _heimdall »

Foreign keys allow for pushing a really vital piece of business logic down to the database itself, referential integrity.

This can be done in application logic, but that risks bugs allowing broken references into your data. Its more foolproof when these checks are enforced directly at the db, making sure data is valid before it's stored or updated.

Re: Do you really need foreign keys?

#20

Post by shayonj »

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…

ah! thats a blast from the past. I maintain pg-osc (online schema change tool for postgres) and very much agree that FKs make OSC hard.
Post reply on HN