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

21–30 of 179 posts

Re: Do you really need foreign keys?

#21

Post by doix »

wombatpm wrote: post #15
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.

If you duplicate data like mad, how do you ensure that the duplicates are in sync? If you avoid UPDATEs I could maybe see it working.

Re: Do you really need foreign keys?

#22

Post by arrowleaf »

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…

By soft deletion, do you mean you would want to ever re-instate the record and make it valid again, PK/FK constraints and all? Or tracking deleted records? For the latter I use audit tables + triggers to track the changing values.

At a basic level you could duplicate the entire record into the audit table on every action, e.g. the audit table would look like `audit_id | record_id | user/process_id | action (insert, update, delete) | timestamp | ...`.

You can optimize it to not duplicating column values unless necessary. On inserts you only need the metadata of the action. On updates, the old value of columns with changes goes into the audit table. On deletes the whole record goes into the audit table.

Re: Do you really need foreign keys?

#24

Post by goostavos »

Follow this advice with caution. Dropping foreign keys is effectively giving up part of the C in ACID. It should be done with very, very open eyes to the downsides. I'm not sure the author is selling the "when" side of this very well. Migrations are "hard" because the database is forcing you to handle correctness criteria that are easy to ignore. "Lock contention" is the database covering your sloppy ill-thought out application code from doing the Wrong Thing. Yes, of course, you take a minor performance hit in exchange for consistency, but how many of us actually work on applications where that trade off is the wrong one? Developers are frustratingly superstitious about scale and their need to do it.

The data will very likely outlive the application that created it. It's almost guaranteed to outlive your tenure on the team. Viciously guarding its correctness solves all the problems that not guarding it causes.

Re: Do you really need foreign keys?

#25

Post by iLoveOncall »

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 question being whether it's at the DB level or at the application level.

I don't think there are many cases where there's any advantage to self-manage them at the application level.

> FKs don't work well with online schema migrations

This seems to be related only to the specific project that the issue is about if you read about the detailed explanation below.

Re: Do you really need foreign keys?

#26

Post by cube2222 »

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?

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 as long as it has anything in the “many” side. Both sides using soft-deletion.

Re: Do you really need foreign keys?

#27

Post by erik_seaberg »

goostavos wrote: post #24

Follow this advice with caution. Dropping foreign keys is effectively giving up part of the C in ACID. It should be done with very, very open eyes to the downsides. I'm not sure the author is selling the "when" side of this very well. Migrations are "hard" because the database is forcing you to handle correctness criteria that are easy to ignore. "Lock contention" is the database covering your sloppy ill-thought out…

Whenever anyone talks about “the application,” I immediately ask “what about all the other applications?” I promise you that you will find customer service and accounting and biz dev have also built stuff that uses the database to get their jobs done (probably not with the same ORM or even the same language) unless you have taken draconian measures to prevent them.

Re: Do you really need foreign keys?

#29

Post by wharvle »

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…

Foreign keys are wholly optional if you’re the kind of person who thinks it’d be cool to add “manually repair millions of rows” and/or “lose half a day of data; cry” to the todo list for a 3AM emergency deployment rollback.

Re: Do you really need foreign keys?

#30

Post by charlie0 »

Having worked in a db with lots of missing FKs, it becomes very difficult to figure out relationships. This makes it difficult to find out what tables need to be updated when there's a lot of them. In this particular company, the only thing holding it together was institutional knowledge.
Post reply on HN