4 ms·

You don’t need foreign keys, no. But you do need referential integrity and foreign keys implemented and enforced by the database is usually the easiest. If you

You don’t need foreign keys, no. But you do need referential integrity and foreign keys implemented and enforced by the database is usually the easiest. If you’re disciplined it’s not difficult to implement yourself in the application, but the challenge is if folks can access the database directly. If so, good luck, since it’s inevitable that they end up making modifications and break integrity. If one insists on not having foreign keys (and there are decent reasons to not have them), I would really really suggest not allowing direct access to the database and enforce whatever application be the main or only client to the database.

shayonj 3y ago
That is great and I noticed it’s something I have always assumed should be the case - no indirect access to the database outside your application / ORM. Because then you open a can of worms.
- out_of_protocol 3y ago
  Doesn't help much though. It only takes not wrapping related inserts into transaction in one place in your code. i know zero frameworks/ORMs that can help you with that
  - tacone 3y ago
    I usually wrap everything (including reads) in a transaction at middleware level. Not perfect, has its downsides but for many projects it's perfectly ok. Of course it's not a real substitute for foreign keys, but definitely better than nothing.
wharvle 3y ago
Foreign keys are wholly optional if you’re the kind of person who thinks it’d be cool to add “manually repair millions of rows” and/or “lose half a day of data; cry” to the todo list for a 3AM emergency deployment rollback.
nonethewiser 3y ago
What would be the advantage of implementing it in the application? I only see disadvantages: - no data integrity check in the database - more complicated definition of foreign relation, or none at all, leaving possibility for deviant data - scatteted schema. Cant look at the db table and understand the entire model. Have to hunt in code an potentially across a lot of code. It just seems like a data integrity issue that needs to be enforced at the db level.
- randomdata 3y ago
  > What would be the advantage of implementing it in the application? It is always implemented in the application. The question is about whether you do it in your own application or use someone else's application. The disadvantage of doing it in your application is that you have to do the work that someone else has probably already done, and are likely not as great of a programmer as that other person, thus more likely to screw it up.
- endisneigh 3y ago
  It’s faster, you can do online schema migrations, but it’s hard to say unless a specific database is used as an example.
- cogman10 3y ago
  The main advantage is it's WAY faster if you are writing a lot. The big disadvantage of foreign keys is they do verify integrity. That means making lookups on every write/update which can be very costly especially as the model becomes more complex. If you have a read heavy application with low levels of writes then by all means put in foreign keys to you hearts content. But if you are at a point where you have millions or billions of records in multiple tables, they simply aren't feasible.
  - mavelikara 3y ago
    But you pay the cost in checking it in the application, as GP said. If so, it simply is moving the cost from db to application layer. Is there a reason the checks can be implemented more efficiently in the application than the DB can?
cosmotic 3y ago
Using an intermediate client would go against many of the claimed performance benefits of not using the FK. One would also likely lose the ACI from ACID. Replicating those features in the intermediate client sure is risky; it would require significant expertise. I'd imagine the implementation would leverage transactions, which would be 'round-trip' to the database server and thus the lock contention would still exist and also be substantially worse (due to the round-trip latency).
dkjaudyeqooe 3y ago
It's basically like saying you're ok with dangling pointers or orphaned records, or that you're awesome enough not to need these "slow", annoying safety features. That's all fine, but we know how this ends up in reality. Checking that child records that can exist only if there is a parent record are deleted when the parent is deleted and that the foreign key you're using in a record actually exists in the pointed-to table, for the price of a single index you very likely need anyway, is hardly a problem. You don't need to cascade deletes or anything, that's a red herring, as is migration issues where you can turn off referential integrity while the process is performed.
bjhartin 3y ago
> but the challenge is if folks can access the database directly This is not the challenge. The challenge is that you think you can do an 'almost-as-good' job of data integrity as the RDBMS designers. Even if you could, you will not always be the person that maintains that code.

shayonj 3y ago

That is great and I noticed it’s something I have always assumed should be the case - no indirect access to the database outside your application / ORM. Because then you open a can of worms.

out_of_protocol 3y ago
Doesn't help much though. It only takes not wrapping related inserts into transaction in one place in your code. i know zero frameworks/ORMs that can help you with that
- tacone 3y ago
  I usually wrap everything (including reads) in a transaction at middleware level. Not perfect, has its downsides but for many projects it's perfectly ok. Of course it's not a real substitute for foreign keys, but definitely better than nothing.

wharvle 3y ago

Foreign keys are wholly optional if you’re the kind of person who thinks it’d be cool to add “manually repair millions of rows” and/or “lose half a day of data; cry” to the todo list for a 3AM emergency deployment rollback.

nonethewiser 3y ago

What would be the advantage of implementing it in the application? I only see disadvantages: - no data integrity check in the database - more complicated definition of foreign relation, or none at all, leaving possibility for deviant data - scatteted schema. Cant look at the db table and understand the entire model. Have to hunt in code an potentially across a lot of code. It just seems like a data integrity issue that needs to be enforced at the db level.

randomdata 3y ago
> What would be the advantage of implementing it in the application? It is always implemented in the application. The question is about whether you do it in your own application or use someone else's application. The disadvantage of doing it in your application is that you have to do the work that someone else has probably already done, and are likely not as great of a programmer as that other person, thus more likely to screw it up.
endisneigh 3y ago
It’s faster, you can do online schema migrations, but it’s hard to say unless a specific database is used as an example.
cogman10 3y ago
The main advantage is it's WAY faster if you are writing a lot. The big disadvantage of foreign keys is they do verify integrity. That means making lookups on every write/update which can be very costly especially as the model becomes more complex. If you have a read heavy application with low levels of writes then by all means put in foreign keys to you hearts content. But if you are at a point where you have millions or billions of records in multiple tables, they simply aren't feasible.
- mavelikara 3y ago
  But you pay the cost in checking it in the application, as GP said. If so, it simply is moving the cost from db to application layer. Is there a reason the checks can be implemented more efficiently in the application than the DB can?

cosmotic 3y ago

Using an intermediate client would go against many of the claimed performance benefits of not using the FK. One would also likely lose the ACI from ACID. Replicating those features in the intermediate client sure is risky; it would require significant expertise. I'd imagine the implementation would leverage transactions, which would be 'round-trip' to the database server and thus the lock contention would still exist and also be substantially worse (due to the round-trip latency).

dkjaudyeqooe 3y ago

It's basically like saying you're ok with dangling pointers or orphaned records, or that you're awesome enough not to need these "slow", annoying safety features. That's all fine, but we know how this ends up in reality. Checking that child records that can exist only if there is a parent record are deleted when the parent is deleted and that the foreign key you're using in a record actually exists in the pointed-to table, for the price of a single index you very likely need anyway, is hardly a problem. You don't need to cascade deletes or anything, that's a red herring, as is migration issues where you can turn off referential integrity while the process is performed.

bjhartin 3y ago

> but the challenge is if folks can access the database directly This is not the challenge. The challenge is that you think you can do an 'almost-as-good' job of data integrity as the RDBMS designers. Even if you could, you will not always be the person that maintains that code.