Ask HN: Do You Use Foreign Keys?
The answer should be obvious, but isn't.
I'm curious to hear real-world experiences that go beyond database design 101 textbooks. Have you faced any tradeoffs, surprises, or regrets around this decision?
The answer should be obvious, but isn't.
I'm curious to hear real-world experiences that go beyond database design 101 textbooks. Have you faced any tradeoffs, surprises, or regrets around this decision?
So just tangentially-related to foreign-key usage?
Here's one: Even if it looks convenient, ON DELETE CASCADE is often not what you want. Customers usually need something much more complicated, so you'll be writing external remove-these-before-those loops and steps anyway.
For example, a platform will never really want to wipe out the entire subgraph of records for a Customer or Product. Instead they'll want to "temporarily deactivate" the account, or at most "mark as permanently disabled, erase some sensitive or bulky information, and insert some audit/reason medatata."
Exceptions might be where the related-tables are purely a technical implementation detail, and don't represent domain objects the business thinks or cares about.
I've never seen a database with FKs that I regretted were there but plenty without FKs that caused issues. Also - generally make sure you index your FKs.
Worked in OLAP data warehousing but we never enforced a FK in my 7 years of experience involving 3 companies :P
[flagged]
[flagged]