Start with them, and if you're lucky enough to scale to a point where they're hurting things, reconsider.
Do you really need foreign keys?
31–40 of 179 posts
Re: Do you really need foreign keys?
#32Follow this advice with caution. Dropping foreign keys is effectively giving up part of the C in ACID. It should be done with very, very open eyes to the downsides. I'm not sure the author is selling the "when" side of this very well. Migrations are "hard" because the database is forcing you to handle correctness criteria that are easy to ignore. "Lock contention" is the database covering your sloppy ill-thought out…
Whenever anyone talks about “the application,” I immediately ask “what about all the other applications?” I promise you that you will find customer service and accounting and biz dev have also built stuff that uses the database to get their jobs done (probably not with the same ORM or even the same language) unless you have taken draconian measures to prevent them.
I would argue that if you have completely different contexts / business lines / concerns (customer service, accounting, biz dev) all directly accessing the same database you have far, far larger architectural concerns that FK will ever hope to address.
Re: Do you really need foreign keys?
#33Foreign keys are often the best (and only) documentation of the data model, and the only one guaranteed not to decay, so this is an important consideration when rethinking them.
That said, I quite like this post and the way the author is thinking critically about software decisions most of us take for granted. Even if you decide that foreign keys are always best, this type of thinking is a great way to improve your understanding.
Re: Do you really need foreign keys?
#34You 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…
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.
Re: Do you really need foreign keys?
#35You 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…
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).
Re: Do you really need foreign keys?
#36Foreign keys are often the best (and only) documentation of the data model, and the only one guaranteed not to decay, so this is an important consideration when rethinking them. That said, I quite like this post and the way the author is thinking critically about software decisions most of us take for granted. Even if you decide that foreign keys are always best, this type of thinking is a great way to improve your u…
Thank you :)
Re: Do you really need foreign keys?
#37Do you really need airbags and safety belts? They add a lot of weight, cost and occupy space.
Re: Do you really need foreign keys?
#38You 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…
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 th…
> 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.
Re: Do you really need foreign keys?
#39You 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…
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 th…
It’s faster, you can do online schema migrations, but it’s hard to say unless a specific database is used as an example.
Re: Do you really need foreign keys?
#40You 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…
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 th…
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.