Hacker News Reader

Comment by spookthesunset | Hacker News Reader

Foreign key violations are data corruption!!! It violates the rules of how the data relates and can and will screw up any number of things that depend on the rules being enforced.

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.

View on HN

> It violates the rules of how the data relates

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.

The whole fact that you made a foreign key implies that that is now a rule of your database. If anything is in that column that does not adhere to the foreign key, that means your database is corrupted.

Yes, and what if you don't make a foreign key?

Then you have an implicit rule that is enforced by a hope and a prayer that some junior dev never makes a mistake, your senior engineers are clairvoyant and understand every single aspect of your systems 100% with zero off-days, your code review process catches every single possible edge case (especially the edge cases that you never knew existed), your QA process is 100% and never makes mistakes, your servers never crash in ways that leave things in an inconsistent state, etc.

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.

That's exactly my experience and viewpoint.

Mine is exactly the opposite, most use cases I'm exposed to do not require FK at all, so I simply never use them.

or you never wanted it in the first place.

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.

> Foreign key violations are 100% data corruption

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.

> Sure, yes, of course, who cares?

The stakeholder from whom you got the requirement on which the logical FK constraint is based, for one.

which may not exist.

Why would you identify an FK relationship other than requirements?

This assumes things depend on the rule being enforced.

Nobody suggests taking an app / db with foreign keys and removing them that is a recipe for disaster.

You can keep saying that, but it does not make it true. Properly modeled data does not need those constraints. Well written software handles these correctly. Mediocrely written software fails and complains loudly. Badly written software might get hosed. You're at just as much risk (or more, in my opinion) of that with bad schema changes as with not having foreign key constraints.

Do you not check input from your javascript front-end before you save it? Even if that front-end does its own validation? (No, you see, but there is only one web front-end.... we don't need the backend to validate the input!!!!)

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.

> In what way is letting the database ensure it isn't getting fed crap any different?

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.

> Reporting and bi data might get hosed.

> 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.

If you want to operate assuming your data is always corrupt because your engineers don't understand how to use the tools provided by their database.... Seems like an awful lot of work to re-invent a wheel that your DB server can solve for you. I guess that is on you though....

Reply on news.ycombinator.com