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

121–130 of 179 posts

Re: Do you really need foreign keys?

#121

Post by Clubber »

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

There was a funny NoSql video that said something to the effect of, "if you are willing to sacrifice everything for speed, just pipe all your data to /dev/null."

Found it: https://www.youtube.com/watch?v=b2F-DItXtZs

Re: Do you really need foreign keys?

#122

Post by butlerm »

setr wrote: post #111
butlerm wrote: post #94

Earlier quoted context omitted.

If you do not particularly care about performance or have a great deal of headroom then database enforcement of referential integrity is great. Alternatively you could just write test cases to check for it and not pay the severe performance penalty. The other major downside of database enforcement of referential integrity is the common need to drop and re-create foreign keys during database schema upgrades and data c…

You’re still going to pay the cost of maintaining referential integrity — you’re just doing it on the app side. You can do it faster by being not-correct — eg you don’t need a lock if you ignore race conditions — but it’s not like the database is arbitrarily slow at doing one of its basic fundamental jobs. Of course, you can just skip the validation altogether and cross your fingers and hope you’re correct, but it’s…

It is quite common for modern databases to have multiversion concurrency so that writers do not block readers. If you do not your transactions should either be awfully short, you should be prepared to wait, or you should implement dirty reads (which are quite common in any case).

Re: Do you really need foreign keys?

#123

Post by _a_a_a_ »

A better post than I expected. The only thing I'd add is that foreign keys can actually improve read times because the optimiser knows it can safely skip certain joins e.g. if you have inner equi-joins between tables a, b and c

    a join b join c
If there is an FK from a to b, and likewise from b to c, and you don't use anything in b, then the optimiser can rewrite this to

    a join c
YMMV

Re: Do you really need foreign keys?

#124

Post by Scubabear68 »

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.

Re: Do you really need foreign keys?

#125

Post by grep_it »

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…

The responder to that issue has also written some blog posts that go into more detail on the subject.

* https://code.openark.org/blog/mysql/things-that-dont-work-we...

* https://code.openark.org/blog/mysql/the-problem-with-mysql-f...

Re: Do you really need foreign keys?

#126

Post by _a_a_a_ »

gokhan wrote: post #23

Title should be "Do you really need Foreign Key constraints?". Foreign Key is the field itself, will still be there without the constraint.

True, a foreign key by itself is just a business requirement. A foreign key constraint is what the database does to enforce that. There's rarely any confusion about the two concepts, though.

Re: Do you really need foreign keys?

#127

Post by betenoire »

I worked for a company where we would not use features that depended on foreign keys (cascading deletes, etc.), but we would define the foreign keys 100% of the time. We ran all of our staging environments with the keys, but when it came to prod our DBA would remove them, among other optimizations. I've not done this at other places that didn't have a dedicated DBA or two, though.

Re: Do you really need foreign keys?

#128

Post by enobrev »

norir wrote: post #6

I once worked somewhere that used rails in lieu of foreign keys. The result was a brittle nightly delete_orphaned records script as well as obscure user visible bugs. My team started adding foreign keys to our records and unsurprisingly caught bugs in our application code that otherwise would have been missed. Personally, I think the default should always be to usr foreign keys and only if you have a genuine scalabil…

I worked on a couple projects in the early aughts like this (php / mysql). Even did it myself for a project and fortunately learned my lesson early enough to fix that very project and rely in the database to handle data integrity.

Always important to remember that while learning from others is important; They're just as human as you are.

Re: Do you really need foreign keys?

#130

Post by knorker »

I used MySQL before foreign key support.

It was a nightmare. The article seems to basically be saying "but it's hard!". Well, I'd rather put down the extra effort so that I don't have corrupt data.

You know what's really hard? Fixing corrupt data.

The data is the most important thing in a database.

Post reply on HN