6 ms·

Foreign keys are so overrated. Write good code and you don’t have to rely on the DB to enforce referential integrity. It’s the equivalent of running test cases

Foreign keys are so overrated. Write good code and you don’t have to rely on the DB to enforce referential integrity. It’s the equivalent of running test cases with every db write.

AdrianB1 4y ago
From experience you are very right. Also from experience, most teams are not good enough to write the code with that quality that you can give up to referential integrity.
vecter 4y ago
I'm curious, how would you obviate the need for foreign keys with "good" code? Can you provide a toy example or a reference to an article so I can understand better? I've used NoSQL databases a long time ago and currently rely on on good ole PostgreSQL, but I'm having a hard time understanding how "good" code can be a better solution for managing relationships between data than a foreign key.
- Yoric 4y ago
  I guess you can use indices instead of foreign keys? And somehow implement all the `ON DELETE CASCADE` manually within any transaction that removes the original row? Not sure how it's "good" code but it could be faster.
  - DasIch 4y ago
    A foreign key doesn't necessarily imply an index. If you are using postgres, you would have to add an index in addition to the foreign key, if you want one. On delete cascade, depending on how many rows it cascades to, can be problematic because it's a very long running blocking operation. That's something one might want to do as a background operation and in batches. Although that won't make it faster.
    - sroussey 4y ago
      Faster in aggregate throughput can often be different from faster for a specific operation. Personally, I find delete on cascade dangerous. I mean, lots of fun for a pen tester, sure…
      - Yoric 4y ago
        > Personally, I find delete on cascade dangerous. I mean, lots of fun for a pen tester, sure… Intriguing. Can you tell me more?
    - Yoric 4y ago
      > A foreign key doesn't necessarily imply an index. If you are using postgres, you would have to add an index in addition to the foreign key, if you want one. Sure, I meant an index in which the key is primary. But, on second thought, that's probably me misreading the GP's message. > On delete cascade, depending on how many rows it cascades to, can be problematic because it's a very long running blocking operation. That's something one might want to do as a background operation and in batches. Although that won't make it faster. That can definitely be a problem (just like destructor deallocation stampedes in C++ or Rust). Still less risky than cascading manually and asynchronously, I suspect.
- berkle4455 4y ago
  > for managing relationships between data than a foreign key. You're conflating the concept of a normalized database with insanely slow DB-enforced referential integrity/foreign keys. Toy example? Sure. Take a well-formed 3NF schema and disable foreign key constraints.
  - vecter 4y ago
    Sorry, assume I'm dense. > Take a well-formed 3NF schema and disable foreign key constraints. I'm familiar with 3NF, but can you expand on how 3NF enables you to remove foreign keys? Or feel free to point me to an article/blog, I don't want to waste your time if it's too much to explain. I did some googling but wasn't sure where to proceed from your post.
    - berkle4455 4y ago
      Say you have a super basic setup: user = {user_id, email} order = {order_id, user_id} order.user_id is a foreign key to user.user_id. That's a perfectly valid and reasonable way to organize things. Enabling RDBMS-enforced foreign key constraints is the issue. It slows everything down dramatically.
      - vecter 4y ago
        I see, thanks. Basically just store the foreign keys yourself as columns in relevant tables and perform the joins in SQL without having the DB enforce FK integrity for every insertion/update/delete. Would it be fair to say that read speeds are unaffected by this?
        berkle4455 4y ago
        Yes. Quite literally the only difference is not enabling foreign key constraints. Read speeds unaffected correct.
      - kgeist 4y ago
        >It slows everything down dramatically How slower is it really? MySQL automically creates indexes for foreign keys so I don't think it slows down "dramatically", just an additional indexed retrieval? Do you know of any benchmarks which show the difference?
- paxys 4y ago
  You can have foreign keys (in the sense of a column on one table pointing to an ID column on another), just without the database itself enforcing the correctness of those relationships on every write.
kgeist 4y ago
"Write good code and you won't have bugs" :) I think it's good practice to enforce consistency rules both in the DB and in code. If you make a mistake in your code, the DB won't allow it, and vice versa.
- vp8989 4y ago
  In theory it is good practice, but in practice enforcing referential integrity is not free and it makes satisfying other non-functional requirements harder. Engineering is about tradeoffs. Foreign keys are overkill in modern architectures where DBs/tables are typically only written to by a single application. They solve a problem that lots of people don't really have anymore.
  - kgeist 4y ago
    In the legacy project our team inherited, there's a lot of data consistency issues stemming from the lack of foreign keys (we're adding them back now). Data consistency is important because we deal with financial data. Typically, code and data change more often than DB schemas, so every release has a chance that someone will forget to update all relations manually in code, introducing dangling references, for example. Foreign keys help as an additional safety measure, because they don't change as often. I think you should care about the performance of foreign keys only when speed is more important than data consistency. It's a good default.
    - sroussey 4y ago
      Personally, I thinks foreign keys are great in dev and bad at any scale that you have many db servers. And as you point out, there are exceptions, like financial data. But not marketing funnels where you might throw everything away. You should probably have different types of engineers working on such different projects as well.
  - erik_seaberg 4y ago
    > DBs/tables are typically only written to by a single application I’ve seen several teams regret making this assumption. Unless you enforce draconian access control over a database, you’re going to discover that customer support and accounting and biz dev have been quietly relying on it.
iamflimflam1 4y ago
In my experience almost every time I've heard people saying they don't want foreign keys because of "the performance hit" - they've been nowhere near the scale or the skill that would make it sensible.
XCSme 4y ago
I don't think foreign keys can be replaced by good code. Probably their main advantage is what happens if there are edge-cases or DB write fails, to make sure the referential integrity is maintained in outstanding circumstances.

Foreign keys are so overrated. Write good code and you don’t have to rely on the DB to enforce referential integrity. It’s the equivalent of running test cases with every db write.

AdrianB1 4y ago

From experience you are very right. Also from experience, most teams are not good enough to write the code with that quality that you can give up to referential integrity.

vecter 4y ago

I'm curious, how would you obviate the need for foreign keys with "good" code? Can you provide a toy example or a reference to an article so I can understand better? I've used NoSQL databases a long time ago and currently rely on on good ole PostgreSQL, but I'm having a hard time understanding how "good" code can be a better solution for managing relationships between data than a foreign key.

Yoric 4y ago
I guess you can use indices instead of foreign keys? And somehow implement all the `ON DELETE CASCADE` manually within any transaction that removes the original row? Not sure how it's "good" code but it could be faster.
- DasIch 4y ago
  A foreign key doesn't necessarily imply an index. If you are using postgres, you would have to add an index in addition to the foreign key, if you want one. On delete cascade, depending on how many rows it cascades to, can be problematic because it's a very long running blocking operation. That's something one might want to do as a background operation and in batches. Although that won't make it faster.
  - sroussey 4y ago
    Faster in aggregate throughput can often be different from faster for a specific operation. Personally, I find delete on cascade dangerous. I mean, lots of fun for a pen tester, sure…
    - Yoric 4y ago
      > Personally, I find delete on cascade dangerous. I mean, lots of fun for a pen tester, sure… Intriguing. Can you tell me more?
  - Yoric 4y ago
    > A foreign key doesn't necessarily imply an index. If you are using postgres, you would have to add an index in addition to the foreign key, if you want one. Sure, I meant an index in which the key is primary. But, on second thought, that's probably me misreading the GP's message. > On delete cascade, depending on how many rows it cascades to, can be problematic because it's a very long running blocking operation. That's something one might want to do as a background operation and in batches. Although that won't make it faster. That can definitely be a problem (just like destructor deallocation stampedes in C++ or Rust). Still less risky than cascading manually and asynchronously, I suspect.
berkle4455 4y ago
> for managing relationships between data than a foreign key. You're conflating the concept of a normalized database with insanely slow DB-enforced referential integrity/foreign keys. Toy example? Sure. Take a well-formed 3NF schema and disable foreign key constraints.
- vecter 4y ago
  Sorry, assume I'm dense. > Take a well-formed 3NF schema and disable foreign key constraints. I'm familiar with 3NF, but can you expand on how 3NF enables you to remove foreign keys? Or feel free to point me to an article/blog, I don't want to waste your time if it's too much to explain. I did some googling but wasn't sure where to proceed from your post.
  - berkle4455 4y ago
    Say you have a super basic setup: user = {user_id, email} order = {order_id, user_id} order.user_id is a foreign key to user.user_id. That's a perfectly valid and reasonable way to organize things. Enabling RDBMS-enforced foreign key constraints is the issue. It slows everything down dramatically.
    - vecter 4y ago
      I see, thanks. Basically just store the foreign keys yourself as columns in relevant tables and perform the joins in SQL without having the DB enforce FK integrity for every insertion/update/delete. Would it be fair to say that read speeds are unaffected by this?
      - berkle4455 4y ago
        Yes. Quite literally the only difference is not enabling foreign key constraints. Read speeds unaffected correct.
    - kgeist 4y ago
      >It slows everything down dramatically How slower is it really? MySQL automically creates indexes for foreign keys so I don't think it slows down "dramatically", just an additional indexed retrieval? Do you know of any benchmarks which show the difference?
paxys 4y ago
You can have foreign keys (in the sense of a column on one table pointing to an ID column on another), just without the database itself enforcing the correctness of those relationships on every write.

kgeist 4y ago

"Write good code and you won't have bugs" :) I think it's good practice to enforce consistency rules both in the DB and in code. If you make a mistake in your code, the DB won't allow it, and vice versa.

vp8989 4y ago
In theory it is good practice, but in practice enforcing referential integrity is not free and it makes satisfying other non-functional requirements harder. Engineering is about tradeoffs. Foreign keys are overkill in modern architectures where DBs/tables are typically only written to by a single application. They solve a problem that lots of people don't really have anymore.
- kgeist 4y ago
  In the legacy project our team inherited, there's a lot of data consistency issues stemming from the lack of foreign keys (we're adding them back now). Data consistency is important because we deal with financial data. Typically, code and data change more often than DB schemas, so every release has a chance that someone will forget to update all relations manually in code, introducing dangling references, for example. Foreign keys help as an additional safety measure, because they don't change as often. I think you should care about the performance of foreign keys only when speed is more important than data consistency. It's a good default.
  - sroussey 4y ago
    Personally, I thinks foreign keys are great in dev and bad at any scale that you have many db servers. And as you point out, there are exceptions, like financial data. But not marketing funnels where you might throw everything away. You should probably have different types of engineers working on such different projects as well.
- erik_seaberg 4y ago
  > DBs/tables are typically only written to by a single application I’ve seen several teams regret making this assumption. Unless you enforce draconian access control over a database, you’re going to discover that customer support and accounting and biz dev have been quietly relying on it.

iamflimflam1 4y ago

In my experience almost every time I've heard people saying they don't want foreign keys because of "the performance hit" - they've been nowhere near the scale or the skill that would make it sensible.

XCSme 4y ago

I don't think foreign keys can be replaced by good code. Probably their main advantage is what happens if there are edge-cases or DB write fails, to make sure the referential integrity is maintained in outstanding circumstances.