Do you really need foreign keys?
shayon.dev
Do you really need foreign keys?
1–10 of 179 posts
Re: Do you really need foreign keys?
#2Unless 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.
Re: Do you really need foreign keys?
#3nice, but I wonder if a query optimizer within the database might be the more optimal solution.
Re: Do you really need foreign keys?
#4On 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.
Re: Do you really need foreign keys?
#5Ahh 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...
Re: Do you really need foreign keys?
#6I 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.
Re: Do you really need foreign keys?
#7On 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…
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.
Re: Do you really need foreign keys?
#8I 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”?
Re: Do you really need foreign keys?
#9I 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 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)
Re: Do you really need foreign keys?
#10On 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?