5 ms·

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 be

by vecter 3y 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 3y 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 3y 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 3y 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 3y 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 3y 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 3y 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 3y 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 3y 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 3y 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 3y ago
        Yes. Quite literally the only difference is not enabling foreign key constraints. Read speeds unaffected correct.
    - kgeist 3y 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 3y 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.

by vecter 3y ago

Yoric 3y 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 3y 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 3y 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 3y 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 3y 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 3y 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 3y 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 3y 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 3y 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 3y ago
      Yes. Quite literally the only difference is not enabling foreign key constraints. Read speeds unaffected correct.
  - kgeist 3y 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 3y 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.