3 ms·

How does the app know what is valid in a foreign key column without repeatedly retrieving the primary key column of the other table?

• tomnipotent 9y ago
  It repeatedly retrieves the parent row. Look at the URL when replying on HN and change the id by adding a few 0's - the app prevents a comment from being added if the ID isn't found. Of course, this doesn't prevent a developer from circumventing the apps constraint logic.
  • gaius 9y ago
    This would place a far higher load on the DB (which would validate the constraint via the index if allowed to) and create more network roundtrips. It’s 10x as expensive as just using a FK, and far less reliable!
    • tomnipotent 9y ago
      >> It’s 10x as expensive as just using a FK It's not about load, it's about locks and contention in the database caused by FK constraint enforcement. Extra read queries will barely be noticeable compared to that.
      • gaius 9y ago
        it's about locks and contention in the database caused by FK constraint enforcement What locks do you mean?