10 ms·

Do you really need foreign keys?

117 points by shayonj 3y ago

KeyBoardG 3y ago
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.
- x86_64Ubuntu 3y ago
  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.
  - eddd-ddde 3y ago
    In my experince, adding proper constraints will not only ensure code can never write invalid data to disk, it will also help you realize when business rules are lacking or failed to consider edge cases.
cyberninja15 3y ago
nice, but I wonder if a query optimizer within the database might be the more optimal solution.
cube2222 3y ago
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 restrict) as long as it has anything in the “many” side. Both sides using soft-deletion.
- michaelmior 3y ago
  I see at least two possible approaches (each with their tradeoffs, neither great): 1. For each relevant foreign key, have an additional column without a constraint where the foreign key value can be copied to before deletion. (This requires that the original foreign key be nullable.) 2. Make soft deletion universal so that foreign keys can remain and any associated rows will still exist.
- stop50 3y ago
  How are foreign keys and deleted_at mutual exclusive?
  - dspillett 3y ago
    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.
  - cube2222 3y ago
    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.
- PeterisP 3y ago
  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.
- arrowleaf 3y ago
  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 | ...<record rows>`. 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.
zer00eyz 3y ago
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...
- wombatpm 3y ago
  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.
  - doix 3y ago
    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.
  - zer00eyz 3y ago
    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.
- Clubber 3y ago
  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 https://www.youtube.com/watch?v=b2F-DItXtZs
norir 3y ago
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 scalability problem might you consider dropping them or put then in a different database with lighter data integrity guarantees.
- shayonj 3y ago
  I agree and makes sense. Starting out with them and then challenging the setup is a good practice. I like the idea of moving to a lighter data integrity setup too (ofc when possible)
- seadan83 3y ago
  This anecdote is not very surprising to me. Foreign keys are on a scale of DB constraints. I opt to add a maximum of constraints where I can. Adding a date that should be in the future - I'm adding a constraint! Did you mess up your time zone conversions & are trying to add a date in the past - the constraint catches it. At some point the constraints are strong enough where with high confidence the following statement can be said: "if the data exists in the database at all, it is correct & complete." This is the "database as fortress" principle, that only correct data should ever be allowed into the database to begin with.
- enobrev 3y ago
  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.
eska 3y ago
I wonder whether there’s a measurable performance difference if instead of calling delete two times he just used the second delete with “on delete cascade”?
dventimi 3y ago
"Do you really need [foreign key constraints]?" FTFY. Foreign keys != Foreign key constraints
endisneigh 3y ago
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.
- shayonj 3y ago
  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.
  - out_of_protocol 3y ago
    Doesn't help much though. It only takes not wrapping related inserts into transaction in one place in your code. i know zero frameworks/ORMs that can help you with that
    - tacone 3y ago
      I usually wrap everything (including reads) in a transaction at middleware level. Not perfect, has its downsides but for many projects it's perfectly ok. Of course it's not a real substitute for foreign keys, but definitely better than nothing.
- wharvle 3y ago
  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.
- nonethewiser 3y ago
rpgmaker 3y ago
50 years onward and every innovation seems to break, rather than improve, the tried and true relational database model.
- arrowleaf 3y ago
  Implementations with skin in the game are still innovating and improving the 'tried and true' relational database model, IMO. PostgreSQL, MS SQL, MySQL, etc. consistently churn out new features that actually make sense, like FK constraint support for sharded/partitioned tables relevant to this discussion.
lrobinovitch 3y ago
This github issue is often linked when this topic is discussed: https://github.com/github/gh-ost/issues/331 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.
- shayonj 3y ago
  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.
- iLoveOncall 3y ago
  > 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.
  - randomdata 3y ago
    > the only question being whether it's at the DB level or at the application level. It is not a binary situation like that. With the rise of 'n-tier' systems that are ever so popular today, there are often multiple DB levels. The question is not so much if it should go into the end user application – pretty much everyone will say definitely not there – but at which DB level it should it go in. That is less clear, and where you will get mixed responses.
_heimdall 3y ago
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.
- kevincox 3y ago
  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.
- PartiallyTyped 3y ago
  There was an interesting topic on r/experienceddevs [1], where the dev team was arguing with some DBAs on adding another column. The DBA insisted on using FKs over enum / application level logic, etc. The comments there present some excellent arguments on top of everything you said. [1] https://www.reddit.com/r/ExperiencedDevs/comments/18ldexi/wrestling_with_dbas_every_time_we_need_to_add_a/ https://www.reddit.com/r/ExperiencedDevs/comments/18ldexi/wr...
gokhan 3y ago
Title should be "Do you really need Foreign Key constraints?". Foreign Key is the field itself, will still be there without the constraint.
- shayonj 3y ago
  Great call!
- LtWorf 3y ago
  And the body should really be "Yes. You do."
- _a_a_a_ 3y ago
  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.
goostavos 3y ago
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.
- erik_seaberg 3y ago
  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.
  - goostavos 3y ago
    I would argue that if you have completely different contexts / business lines / concerns (customer service, accounting, biz dev) all directly accessing the same database you have far, far larger architectural concerns that FK will ever hope to address.
    - eddd-ddde 3y ago
      I strongly believe the opposite. Your business IS your database. How do you handle access to the data without access to the data? It is inevitable that 2 "organizations" will at some point need access to the same data. You could just have each of them decide how they interact with it. It doesn't matter how they do it because the database itself makes sure invariants are kept true (such as FKs). What's the alternative?
charlie0 3y ago
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.
davidw 3y ago
Start with them, and if you're lucky enough to scale to a point where they're hurting things, reconsider.
- arrowleaf 3y ago
  And then, don't just drop the constraints because you read an article about how FK constraints don't work with a distributed relational model, take a careful look at the docs of whatever DB engine you're using. PostgreSQL 12 for example supports foreign keys with either side being a partitioned table.
jamestimmins 3y ago
Foreign keys are often the best (and only) documentation of the data model, and the only one guaranteed not to decay, so this is an important consideration when rethinking them. That said, I quite like this post and the way the author is thinking critically about software decisions most of us take for granted. Even if you decide that foreign keys are always best, this type of thinking is a great way to improve your understanding.
- shayonj 3y ago
  Thank you :)
- claytongulick 3y ago
  I agree - and I also appreciate the author's objectivity and focus on making the correct technical decision for the problem. Dogma is the bane of good architecture. I completely agree with the idea that foreign keys aren't always recommended. For example, one of the reasons that MongoDB was so great at edge writes was because of the lack of referential integrity and the "document" as a row concept - denormalize the data into a document and store everything you need there. That works great for high volume edge writes that you want to operationalize later, asynchronously. Sure, the data can be messy and may have errors - but many use cases tolerate this well. The nice thing about modern postgres is that you get the best of both worlds, RDBMS stuff when you need it, and NoSQL stuff if you don't. The author's point, which I think is an excellent one, is that just because it's in a database, it doesn't necessarily make sense to go full 4th normal form on everything.
- seadan83 3y ago
  +1 for that documentation. I was recently working on a system that split data across multiple database instances (unnecessarily) and that means referential integrity is lacking and sometimes a huge problem. To expound, I was tracking down something call a "slotid" and the absence of foreign keys does not mean anything. That data could very well live in some other table on the other DB instance. It turned out though that "slot-id" instead referred to a start time of day as counted by 'minutes-from-midnight'. Thus "slot-id=375" was just "6:15am"... Yup.. 3 hours of my life I will not get back to realize that data did not refer to anything at all. If everything that could have had foreign keys did, then it would have been a super quick investigation to realize the data was not a reference to anything at all.
belltaco 3y ago
Do you really need airbags and safety belts? They add a lot of weight, cost and occupy space.
ww520 3y ago
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.
- arrowleaf 3y ago
  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.
- crazygringo 3y ago
  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.
- magicalhippo 3y ago
  Might be a performance penalty as well. We had some missing FK constraints, along with missing cleanup in code for a given table. Some customers had many millions of orphaned rows, while only thousands of live rows. Certainly didn't help index scans...
- arp242 3y ago
  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 deletes on the parent ID, but just having soft deletes sufficiently alleviates that (potential) small issue.
  - ww520 3y ago
    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.
pphysch 3y ago
I'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.
deleted 3y ago
[deleted]
amtamt 3y ago
This post seems like long version of "Directive 595 Part 2" from https://thedailywtf.com/articles/directive-595 https://thedailywtf.com/articles/directive-595 Anyone planning to test "YMMV", or full blown follow through, is advised to read that dailywtf.
BenoitP 3y ago
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 are caught, then data gets pruned heavily) If referential integrity is a business-level bug, then of course we should enable them.
- shayonj 3y ago
  Very interesting! Thanks for sharing
- somehnguy 3y ago
  To me that seems like an invitation for catastrophic bugs or problems to creep through and surprise you in prod, since the lower envs are running different a different configuration. I suppose it's entirely dependent on the type of data you're working with though.
- LtWorf 3y ago
  > as long as it's only about not dropping user data Can you tell the name of the company? It's for my lawyer.
- HideousKojima 3y ago
  >My rule of thumb has been: enable them strictly in DEV and INT environments, disable in PROD I mean I think the constraints should be on in all environments, but disabling them in prod but not dev seems utterly backwards? Protect your test data from getting corrupted but not your actual customer data?
  - solumunus 3y ago
    I assume the idea is that data corruption issues will be spotted in dev testing.
binarymax 3y ago
How often is data actually DELETEd from production databases? Unless following through with regulatory removal such as GDPR, it’s far better to use an isdeleted flag IMO (so you can un-delete if the action was a mistake).
- pphysch 3y ago
  That's true, but managing the "soft" referential integrity becomes a big authorization/security headache. How do you prevent/allow "deleted" objects from being accessed indirectly? I like Django's ORM approach which allows you to easily set baseline filters for your Model Managers, so you can implicitly exclude "deleted" objects from most queries.
  - binarymax 3y ago
    Good point. I’m one of those “never-ORM” people, so writing & maintaining the SQL is prone to error when the DB gets complex, and it does introduce mental overhead during dev.
- arp242 3y ago
  There's also an impact on inserts; it's not just deletes. Essentially a foreign key constraint is an on {insert,update,delete} trigger which checks whether the target check exists, so that's a select on the target table. I'm not sure if that's still the case, but I believe that for a long time foreign keys were just implemented as triggers in PostgreSQL. For a lot of things that has a minimal performance impact and it's not a big deal. For some other things it can really add up.
  - binarymax 3y ago
    FKs are typically primary keys/clustered indices on the related table, so in those cases the integrity overhead would be very minimal. So in the vast majority of cases, the integrity is worth it.
    - arp242 3y ago
karmakaze 3y ago
Large scale MySQL databases I've worked on typically do not use foreign keys. The payoff is higher write performance. They're implicit based on table/column naming and relationships defined in code, e.g. Rails associations or process/operation classes. With a certain level of team maturity and thoughtful reviews, this has rarely been an issue. Sometimes there are orphaned rows (from incomplete/buggy writes) which also have clean-ups that tend to have jobs for pruning data that has gone 'out of the retention window'.
- marcosdumay 3y ago
  > this has rarely been an issue I have seen many people running MySQL make that claim about lots of things... But what I have never seen is a MySQL database for business data without major issues.
- sgarland 3y ago
  Depends on your definition of large-scale, but I managed a 120K QPS MySQL 8.x cluster that heavily used FKs. After heavy optimization and parameter tuning, I was also able to halve the instance size while improving query performance. I’m a big fan of FKs. They stop you from doing stupid shit, and if the time ever truly comes that you have to drop them, you can do so.
toasted-subs 3y ago
Something about a secret racist thing about the word "foreign". Personally I wouldn't mind working with more Spanish, Italian, and French people. Not complaining about the types of coworkers I see, but sometimes tapas are kind of nice.
6R1M0R4CL3 3y ago
[dead]
simonw 3y ago
If you work on a large scale system that doesn't include foreign key constraints, try running some queries that should never return results because that would expose data integrity problems. If your system is hooked up to a data warehouse you can run queries there, too. I bet you can find all sorts of weird edge-case records doing this.
iamflimflam1 3y ago
Whenever I read posts like this I know, despite all the caveats at the start of the article, a large number of people will take away "we shouldn't have any foreign keys because they impact performance". I suggest that anyone thinking that has a look at a database where there are no foreign keys.
- yen223 3y ago
  Looking at the comments here, almost everyone here are saying "you must ALWAYS use foreign key constraints", which I think is also not correct.
forinti 3y ago
A colleague once insisted in not using FKs because he read somewhere that that was the modern way of doing things. So the universe decided to punish him and a few weeks later some data got deleted that would have been protected by an FK. This idea is no longer discussed.
bhaak 3y ago
I’m missing the execution plans of these DELETE statements. Without them it‘s not clear what‘s the problem. But in any case sacrificing referential integrity for speed is a bad idea. You could go NoSQL in that case anyway.
jelled 3y ago
I see foreign keys the same way I see types. You may not need them, but they catch a lot of mistakes and give you more confidence in the integrity of your system.
cdcarter 3y ago
The Salesforce CRM application somewhat "famously" does not use native DB foreign keys to model most relationships, and has a custom relational integrity and indexing layer. Mostly, this is in order to support our complex custom schema functionality, described in the Multitenant Whitepaper here (https://www.developerforce.com/media/ForcedotcomBookLibrary/Force.com_Multitenancy_WP_101508.pdf https://www.developerforce.com/media/ForcedotcomBookLibrary/...) But interestingly, this also lets our internal data modelers choose to make a relationship e.g. leave dangling records or have other update/delete patterns that are appropriate for the data and data volume. Obviously there are cleanups and tradeoffs required here. It's certainly not a model I'd suggest people start with, but there really are times where database native FKs aren't the answer.
- jamestimmins 3y ago
  Do you think this has served y'all well in the long term? I assume "internal data modelers" is an actual role of person whose job is to ensure long run data integrity?
- oxfordmale 3y ago
  Salesforce storage is about 1000 times more expensive than regular storage, so might not be the best example. Data volume and Salesforce also don't belong in the same sentence, as it is not comparable to the data volume a basic Postgres database can handle. Until recently Salesforce didn't provide a data backup facility, other than exporting CSVs, so not even sure you can call it a database.
_a_a_a_ 3y ago
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
- senderista 3y ago
  Yes, I remember using foreign key relationships to optimize symmetric hash join back when I worked on an OLAP db. The idea was that for a 1:1 relationship you can immediately discard both sides of a joined tuple, while for a 1:many relationship you can immediately discard the "many" side of a joined tuple.
  - _a_a_a_ 3y ago
    thanks for the detail!
Scubabear68 3y ago
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.
- boobsbr 3y ago
  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.
betenoire 3y ago
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.
ceeam 3y ago
Yes. And if you're concerned for microspeed of basic operations Postgres isn't your friend anyway.
knorker 3y ago
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.
jbverschoor 3y ago
Well, do we really need protected memory and bounds checking?
mastazi 3y ago
Despite the title, this article is about dropping foreign key constraints, not about dropping foreign keys altogether (which is possible, see below). You can drop foreign key constraints and enforce referential integrity in your code, but from a logical standpoint your foo.barId column is still a foreign key. Actually getting rid of foreign keys on the other hand can be done in a data store that allows nested structures, like e.g. a document based store. The price to pay is data duplication and denormalisation (and the related complexity you will have to deal with in all write operations), the pros are ease and speed of retrieval.
prudentpomelo 3y ago
Referential integrity saves you so many headaches. I am dealing with a 500+ table database with no foreign keys at all. This has allowed so many problems to creep in. Writing queries is pure suffering.
pif 3y ago
At last, a counterexample to Betteridge's law of headlines!
mikl 3y ago
Do you really need referential integrity? Do you really need consistent data? Do you really need test coverage? I mean, you can make do without it. But it’s generally better to have it than not.
carcabob 3y ago
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.
- sseagull 3y ago
  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).
redandblack 3y ago
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

117 points by shayonj 3y ago

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.

x86_64Ubuntu 3y ago
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.
- eddd-ddde 3y ago
  In my experince, adding proper constraints will not only ensure code can never write invalid data to disk, it will also help you realize when business rules are lacking or failed to consider edge cases.

cyberninja15 3y ago

nice, but I wonder if a query optimizer within the database might be the more optimal solution.

cube2222 3y ago

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

michaelmior 3y ago
I see at least two possible approaches (each with their tradeoffs, neither great): 1. For each relevant foreign key, have an additional column without a constraint where the foreign key value can be copied to before deletion. (This requires that the original foreign key be nullable.) 2. Make soft deletion universal so that foreign keys can remain and any associated rows will still exist.
stop50 3y ago
How are foreign keys and deleted_at mutual exclusive?
- dspillett 3y ago
  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.
- cube2222 3y ago
  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.
PeterisP 3y ago
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.
arrowleaf 3y ago
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 | ...<record rows>`. 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.

zer00eyz 3y ago

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

wombatpm 3y ago
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.
- doix 3y ago
  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.
- zer00eyz 3y ago
  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.
Clubber 3y ago
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 https://www.youtube.com/watch?v=b2F-DItXtZs

norir 3y ago

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 scalability problem might you consider dropping them or put then in a different database with lighter data integrity guarantees.

shayonj 3y ago
I agree and makes sense. Starting out with them and then challenging the setup is a good practice. I like the idea of moving to a lighter data integrity setup too (ofc when possible)
seadan83 3y ago
This anecdote is not very surprising to me. Foreign keys are on a scale of DB constraints. I opt to add a maximum of constraints where I can. Adding a date that should be in the future - I'm adding a constraint! Did you mess up your time zone conversions & are trying to add a date in the past - the constraint catches it. At some point the constraints are strong enough where with high confidence the following statement can be said: "if the data exists in the database at all, it is correct & complete." This is the "database as fortress" principle, that only correct data should ever be allowed into the database to begin with.
enobrev 3y ago
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.

eska 3y ago

I wonder whether there’s a measurable performance difference if instead of calling delete two times he just used the second delete with “on delete cascade”?

dventimi 3y ago

"Do you really need [foreign key constraints]?" FTFY. Foreign keys != Foreign key constraints

endisneigh 3y ago

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.

shayonj 3y ago
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.
- out_of_protocol 3y ago
  Doesn't help much though. It only takes not wrapping related inserts into transaction in one place in your code. i know zero frameworks/ORMs that can help you with that
  - tacone 3y ago
    I usually wrap everything (including reads) in a transaction at middleware level. Not perfect, has its downsides but for many projects it's perfectly ok. Of course it's not a real substitute for foreign keys, but definitely better than nothing.
wharvle 3y ago
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.
nonethewiser 3y ago

rpgmaker 3y ago

50 years onward and every innovation seems to break, rather than improve, the tried and true relational database model.

arrowleaf 3y ago
Implementations with skin in the game are still innovating and improving the 'tried and true' relational database model, IMO. PostgreSQL, MS SQL, MySQL, etc. consistently churn out new features that actually make sense, like FK constraint support for sharded/partitioned tables relevant to this discussion.

lrobinovitch 3y ago

This github issue is often linked when this topic is discussed: https://github.com/github/gh-ost/issues/331 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.

shayonj 3y ago
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.
iLoveOncall 3y ago
> 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.
- randomdata 3y ago
  > the only question being whether it's at the DB level or at the application level. It is not a binary situation like that. With the rise of 'n-tier' systems that are ever so popular today, there are often multiple DB levels. The question is not so much if it should go into the end user application – pretty much everyone will say definitely not there – but at which DB level it should it go in. That is less clear, and where you will get mixed responses.

_heimdall 3y ago

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.

kevincox 3y ago
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.
PartiallyTyped 3y ago
There was an interesting topic on r/experienceddevs [1], where the dev team was arguing with some DBAs on adding another column. The DBA insisted on using FKs over enum / application level logic, etc. The comments there present some excellent arguments on top of everything you said. [1] https://www.reddit.com/r/ExperiencedDevs/comments/18ldexi/wrestling_with_dbas_every_time_we_need_to_add_a/ https://www.reddit.com/r/ExperiencedDevs/comments/18ldexi/wr...

gokhan 3y ago

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

shayonj 3y ago
Great call!
LtWorf 3y ago
And the body should really be "Yes. You do."
_a_a_a_ 3y ago
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.

goostavos 3y ago

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.

erik_seaberg 3y ago
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.
- goostavos 3y ago
  I would argue that if you have completely different contexts / business lines / concerns (customer service, accounting, biz dev) all directly accessing the same database you have far, far larger architectural concerns that FK will ever hope to address.
  - eddd-ddde 3y ago
    I strongly believe the opposite. Your business IS your database. How do you handle access to the data without access to the data? It is inevitable that 2 "organizations" will at some point need access to the same data. You could just have each of them decide how they interact with it. It doesn't matter how they do it because the database itself makes sure invariants are kept true (such as FKs). What's the alternative?

charlie0 3y ago

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.

davidw 3y ago

Start with them, and if you're lucky enough to scale to a point where they're hurting things, reconsider.

arrowleaf 3y ago
And then, don't just drop the constraints because you read an article about how FK constraints don't work with a distributed relational model, take a careful look at the docs of whatever DB engine you're using. PostgreSQL 12 for example supports foreign keys with either side being a partitioned table.

jamestimmins 3y ago

Foreign keys are often the best (and only) documentation of the data model, and the only one guaranteed not to decay, so this is an important consideration when rethinking them. That said, I quite like this post and the way the author is thinking critically about software decisions most of us take for granted. Even if you decide that foreign keys are always best, this type of thinking is a great way to improve your understanding.

shayonj 3y ago
Thank you :)
claytongulick 3y ago
I agree - and I also appreciate the author's objectivity and focus on making the correct technical decision for the problem. Dogma is the bane of good architecture. I completely agree with the idea that foreign keys aren't always recommended. For example, one of the reasons that MongoDB was so great at edge writes was because of the lack of referential integrity and the "document" as a row concept - denormalize the data into a document and store everything you need there. That works great for high volume edge writes that you want to operationalize later, asynchronously. Sure, the data can be messy and may have errors - but many use cases tolerate this well. The nice thing about modern postgres is that you get the best of both worlds, RDBMS stuff when you need it, and NoSQL stuff if you don't. The author's point, which I think is an excellent one, is that just because it's in a database, it doesn't necessarily make sense to go full 4th normal form on everything.
seadan83 3y ago
+1 for that documentation. I was recently working on a system that split data across multiple database instances (unnecessarily) and that means referential integrity is lacking and sometimes a huge problem. To expound, I was tracking down something call a "slotid" and the absence of foreign keys does not mean anything. That data could very well live in some other table on the other DB instance. It turned out though that "slot-id" instead referred to a start time of day as counted by 'minutes-from-midnight'. Thus "slot-id=375" was just "6:15am"... Yup.. 3 hours of my life I will not get back to realize that data did not refer to anything at all. If everything that could have had foreign keys did, then it would have been a super quick investigation to realize the data was not a reference to anything at all.

belltaco 3y ago

Do you really need airbags and safety belts? They add a lot of weight, cost and occupy space.

ww520 3y ago

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.

arrowleaf 3y ago
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.
crazygringo 3y ago
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.
magicalhippo 3y ago
Might be a performance penalty as well. We had some missing FK constraints, along with missing cleanup in code for a given table. Some customers had many millions of orphaned rows, while only thousands of live rows. Certainly didn't help index scans...
arp242 3y ago
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 deletes on the parent ID, but just having soft deletes sufficiently alleviates that (potential) small issue.
- ww520 3y ago
  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.

pphysch 3y ago

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

deleted 3y ago

[deleted]

amtamt 3y ago

This post seems like long version of "Directive 595 Part 2" from https://thedailywtf.com/articles/directive-595 https://thedailywtf.com/articles/directive-595 Anyone planning to test "YMMV", or full blown follow through, is advised to read that dailywtf.

BenoitP 3y ago

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 are caught, then data gets pruned heavily) If referential integrity is a business-level bug, then of course we should enable them.

shayonj 3y ago
Very interesting! Thanks for sharing
somehnguy 3y ago
To me that seems like an invitation for catastrophic bugs or problems to creep through and surprise you in prod, since the lower envs are running different a different configuration. I suppose it's entirely dependent on the type of data you're working with though.
LtWorf 3y ago
> as long as it's only about not dropping user data Can you tell the name of the company? It's for my lawyer.
HideousKojima 3y ago
>My rule of thumb has been: enable them strictly in DEV and INT environments, disable in PROD I mean I think the constraints should be on in all environments, but disabling them in prod but not dev seems utterly backwards? Protect your test data from getting corrupted but not your actual customer data?
- solumunus 3y ago
  I assume the idea is that data corruption issues will be spotted in dev testing.

binarymax 3y ago

How often is data actually DELETEd from production databases? Unless following through with regulatory removal such as GDPR, it’s far better to use an isdeleted flag IMO (so you can un-delete if the action was a mistake).

pphysch 3y ago
That's true, but managing the "soft" referential integrity becomes a big authorization/security headache. How do you prevent/allow "deleted" objects from being accessed indirectly? I like Django's ORM approach which allows you to easily set baseline filters for your Model Managers, so you can implicitly exclude "deleted" objects from most queries.
- binarymax 3y ago
  Good point. I’m one of those “never-ORM” people, so writing & maintaining the SQL is prone to error when the DB gets complex, and it does introduce mental overhead during dev.
arp242 3y ago
There's also an impact on inserts; it's not just deletes. Essentially a foreign key constraint is an on {insert,update,delete} trigger which checks whether the target check exists, so that's a select on the target table. I'm not sure if that's still the case, but I believe that for a long time foreign keys were just implemented as triggers in PostgreSQL. For a lot of things that has a minimal performance impact and it's not a big deal. For some other things it can really add up.
- binarymax 3y ago
  FKs are typically primary keys/clustered indices on the related table, so in those cases the integrity overhead would be very minimal. So in the vast majority of cases, the integrity is worth it.
  - arp242 3y ago

karmakaze 3y ago

Large scale MySQL databases I've worked on typically do not use foreign keys. The payoff is higher write performance. They're implicit based on table/column naming and relationships defined in code, e.g. Rails associations or process/operation classes. With a certain level of team maturity and thoughtful reviews, this has rarely been an issue. Sometimes there are orphaned rows (from incomplete/buggy writes) which also have clean-ups that tend to have jobs for pruning data that has gone 'out of the retention window'.

marcosdumay 3y ago
> this has rarely been an issue I have seen many people running MySQL make that claim about lots of things... But what I have never seen is a MySQL database for business data without major issues.
sgarland 3y ago
Depends on your definition of large-scale, but I managed a 120K QPS MySQL 8.x cluster that heavily used FKs. After heavy optimization and parameter tuning, I was also able to halve the instance size while improving query performance. I’m a big fan of FKs. They stop you from doing stupid shit, and if the time ever truly comes that you have to drop them, you can do so.

toasted-subs 3y ago

Something about a secret racist thing about the word "foreign". Personally I wouldn't mind working with more Spanish, Italian, and French people. Not complaining about the types of coworkers I see, but sometimes tapas are kind of nice.

6R1M0R4CL3 3y ago

[dead]

simonw 3y ago

If you work on a large scale system that doesn't include foreign key constraints, try running some queries that should never return results because that would expose data integrity problems. If your system is hooked up to a data warehouse you can run queries there, too. I bet you can find all sorts of weird edge-case records doing this.

iamflimflam1 3y ago

Whenever I read posts like this I know, despite all the caveats at the start of the article, a large number of people will take away "we shouldn't have any foreign keys because they impact performance". I suggest that anyone thinking that has a look at a database where there are no foreign keys.

yen223 3y ago
Looking at the comments here, almost everyone here are saying "you must ALWAYS use foreign key constraints", which I think is also not correct.

forinti 3y ago

A colleague once insisted in not using FKs because he read somewhere that that was the modern way of doing things. So the universe decided to punish him and a few weeks later some data got deleted that would have been protected by an FK. This idea is no longer discussed.

bhaak 3y ago

I’m missing the execution plans of these DELETE statements. Without them it‘s not clear what‘s the problem. But in any case sacrificing referential integrity for speed is a bad idea. You could go NoSQL in that case anyway.

jelled 3y ago

I see foreign keys the same way I see types. You may not need them, but they catch a lot of mistakes and give you more confidence in the integrity of your system.

cdcarter 3y ago

The Salesforce CRM application somewhat "famously" does not use native DB foreign keys to model most relationships, and has a custom relational integrity and indexing layer. Mostly, this is in order to support our complex custom schema functionality, described in the Multitenant Whitepaper here (https://www.developerforce.com/media/ForcedotcomBookLibrary/Force.com_Multitenancy_WP_101508.pdf https://www.developerforce.com/media/ForcedotcomBookLibrary/...) But interestingly, this also lets our internal data modelers choose to make a relationship e.g. leave dangling records or have other update/delete patterns that are appropriate for the data and data volume. Obviously there are cleanups and tradeoffs required here. It's certainly not a model I'd suggest people start with, but there really are times where database native FKs aren't the answer.

jamestimmins 3y ago
Do you think this has served y'all well in the long term? I assume "internal data modelers" is an actual role of person whose job is to ensure long run data integrity?
oxfordmale 3y ago
Salesforce storage is about 1000 times more expensive than regular storage, so might not be the best example. Data volume and Salesforce also don't belong in the same sentence, as it is not comparable to the data volume a basic Postgres database can handle. Until recently Salesforce didn't provide a data backup facility, other than exporting CSVs, so not even sure you can call it a database.

_a_a_a_ 3y ago

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

senderista 3y ago
Yes, I remember using foreign key relationships to optimize symmetric hash join back when I worked on an OLAP db. The idea was that for a 1:1 relationship you can immediately discard both sides of a joined tuple, while for a 1:many relationship you can immediately discard the "many" side of a joined tuple.
- _a_a_a_ 3y ago
  thanks for the detail!

Scubabear68 3y ago

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.

boobsbr 3y ago
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.

betenoire 3y ago

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.

ceeam 3y ago

Yes. And if you're concerned for microspeed of basic operations Postgres isn't your friend anyway.

knorker 3y ago

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.

jbverschoor 3y ago

Well, do we really need protected memory and bounds checking?

mastazi 3y ago

Despite the title, this article is about dropping foreign key constraints, not about dropping foreign keys altogether (which is possible, see below). You can drop foreign key constraints and enforce referential integrity in your code, but from a logical standpoint your foo.barId column is still a foreign key. Actually getting rid of foreign keys on the other hand can be done in a data store that allows nested structures, like e.g. a document based store. The price to pay is data duplication and denormalisation (and the related complexity you will have to deal with in all write operations), the pros are ease and speed of retrieval.

prudentpomelo 3y ago

Referential integrity saves you so many headaches. I am dealing with a 500+ table database with no foreign keys at all. This has allowed so many problems to creep in. Writing queries is pure suffering.

pif 3y ago

At last, a counterexample to Betteridge's law of headlines!

mikl 3y ago

Do you really need referential integrity? Do you really need consistent data? Do you really need test coverage? I mean, you can make do without it. But it’s generally better to have it than not.

carcabob 3y ago

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.

sseagull 3y ago
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).

redandblack 3y ago

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