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.
Do you really need foreign keys?
41–50 of 179 posts
Re: Do you really need foreign keys?
#42Follow 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.
My assumption (with modern applications!) is that nothing but the role directly owning the data will access the data. The development and DBA teams will likely have a role they can assume after performing a carefully-audited breakglass procedure to use in an emergency (rare) or to fulfill audit tasks. At least in my org this is a well-known problem with legacy applications sharing databases. Limit access to the database to a single role, used by a single application, and you absolve so many issues.
Re: Do you really need foreign keys?
#43I've been on both sides of this fence.
I work on a legacy MySQL database that has 100+ moderatedly-related tables with zero foreign key constraints.
Someone wanted to change the name of a particular object, so I did a little investigation and found that approximately two dozen tables were storing this name-as-key, with at least half a dozen different column names. So essentially we have to scan every row in the entire database to robustly maintain referential integrity, and I vetoed the largely cosmetic change. Granted, this isn't 100% a FK constraint issue, but I imagine if the original DBA knew what FKs were, I wouldn't be in this mess.
On the other hand, I have implemented prototypes that clearly had way too many FK constraints, which leads to a bloated schema of unnecessary tables with 1-2 columns each, and makes object lifecycles a spaghetti nightmare.
Re: Do you really need foreign keys?
#44The 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.
The only time I could see it really impacting performance is during bulk operations. But that's where making FK constraints deferred and wrapping the bulk actions in a transaction makes more sense.
Re: Do you really need foreign keys?
#45Ahh 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.
MyISAM I think was the table type that did NOT support FK constraints and was blazing fast. InnoDB had FK constraints.
You would fully normalize, use proper joins etc. But without FK constraints you could end up with orphan data... Not the worst thing in the world, depending on how the joins in your system were structured.
Care and diligence were the order of the day. You had to know your schema and make sure you were doing the RIGHT thing at all points in the stack.
Re: Do you really need foreign keys?
#46[deleted]
Re: Do you really need foreign keys?
#47This post seems like long version of "Directive 595 Part 2" from https://thedailywtf.com/articles/directive-595
Anyone planning to test "YMMV", or full blown follow through, is advised to read that dailywtf.
Re: Do you really need foreign keys?
#48Foreign 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.
No to mention that most SQL databases have complex consistency rules. Just checking that the target row exists (or not) before adding (or deleting) a record may not result in a correct database state in the face of concurrent transactions.
You better very carefully read your database's concurrency guarantees and ensure that all transactions are running with the right consistency level otherwise you will have a bad day.
On top of that the JOIN method of following foreign keys often leads to missing rows if expected keys don't exist which can be very hard to debug.
Re: Do you really need foreign keys?
#49My 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 are caught, then data gets pruned heavily)
If referential integrity is a business-level bug, then of course we should enable them.
Re: Do you really need foreign keys?
#50The 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.
Not really -- in practice, it generally means all inserts are done as a transaction, and deletes simply aren't done.
Then there isn't really any opportunity for inconsistent data.