Application code has bugs. Application code can fail in ways that result in corruption. A primary job of the database is to keep itself from getting corrupted. Enforcing foreign key violations is only something the database can do correctly. Punting the responsibility to a higher layer will result in corruption.
That would be where the road splits. If you handle the database as a very fast and structured storage application, a lot of these assumptions go away, with then a different set of tradeoffs.
Some data corruption could be fine if you can guarantee the critical cases, just as bugs in the code are fine as long as the useful cases are covered.
I remember a database with scheduling entries in it that could get duplicated depending on the sharding, but it didn’t matter because the app handled the case gracefully.
I think understanding the tradeoffs that match the best the use case is the most important point, always assuming that a DB has to guarantee integrity can be a burden preventing from looking at all the options.
They have engineered for, and understand the implications of, foreign key violations. Typically, it's as simple as "This row can be deleted", and that can cascade - at a totally different rate than you'd find in a database and with totally different performance characteristics.
Reporting and bi data might get hosed.
Account management might get hosed.
Who knows what happens when FK rules are violated because by definition they should never be violated. It puts all applications on top into a undefined state, leading to bugs and god knows what else.
Foreign key violations are 100% data corruption.
You're missing an important point, it violates a certain set of rules of how the data relates. That certain set of rules typically makes it easier to write most general data applications.
If your rules define a foreign key as optional, you can easily engineer an application around it.
Great examples include NoSQL and Ruby duck-typing. In both cases, you infer actions based on the data structure, rather than making explicit assumptions.
Or you could, you know, simply add a foreign key constraint and never, ever, ever have corrupt table relations.
Why people fight their tools is beyond me. There is almost zero reason to defend not using foreign keys.
i suggest being open to the concept that other perfectly capable humans may, in fact, design systems with different underpinning assumptions than those you appear to presuppose always must apply to everyone and everything.
It's important to remove the DBA or Developer hat and realize that you are working together on a singular system (or maybe it's even the same person, etc.)
"corruption" implies that the desired results have not been achieved, that essential data has been lost or compromised, and this clearly is not the case with such a deliberate design decision. one should be prepared to accept this possibility in order to not be merely a fanatic and unreasonable.
Sure, yes, of course, who cares?
These are self-imposed rules, so breaking them is wholly up to yourself. And there are trade-offs involved that may often make it acceptable relax them.
You seem to be rather invested in one set of arbitrary definitions. If people do fine even in situations where they "by definition" shouldn't, it's the definitions that have been found lacking, not the people.
The stakeholder from whom you got the requirement on which the logical FK constraint is based, for one.
In what way is letting the database ensure it isn't getting fed crap any different?
Why do developers constantly think it is okay to let unvalidated user input hit their database? Any client calling your database is a hostile client that will feed your database bad data. Arguing otherwise is complete ignorance.
Because one canonical validation layer is generally enough. You can see how having two separate partial validation layers could cause problems, right? And you can't put all the validation in the database, for non-trivial apps.
And "clients" shouldn't be talking to the database, no matter if you have foreign keys or not. That's a totally separate issue.
> Account management might get hosed.
LOL, if I read the data directly from the db instead of via the application's API then sure, I lose the application's guarantees. But, y'know, same might be said if I just go and read the DBs files from a sidecar shell script or something.
> It puts all applications on top into a undefined state
...that's just an assumption that's false.
Nobody suggests taking an app / db with foreign keys and removing them that is a recipe for disaster.
Will result in more fault tolerant software, also yes.
It's a trade off and one I make willingly at every scale.
I stopped using foreign keys after university and have never wanted them since.
Non nullable database fields are far more useful than worrying about fks.
More fault tolerant because you have to waste time debugging the faults and monkeypatching them in code just to avoid FKs?
> Non nullable database fields are far more useful than worrying about fks
Can’t even count how many non-nullable fields I’ve seen packed with “” empty strings to get around that requirement
Further to that, instead of having lazy cascading deletes moving the goal posts on you. Data missing its parent for example is an indication something is wrong. It's a useful diagnostic.
2. Most string data is safe to represent as empty. Handling null on the other hand is a different situation and often induces warnings or other side effects depending on the language. Forcing the null checking to your boundaries is much like forcing your state/mutation to the boundaries in FP. Hell eveb though I think it's insane, hexagonal arch works on this idea as well.