Re:MySQL Bug #11472: Triggers not executed following foreign...
by ThinkBeat on Sunday June 21, 2015 @04:35PM
(#9754900)
This might be the case if you have one and only one application interacting with the database, and your unit tests include each and every permutation/edge case.
It is very common to have many applications, from different vendors and from different time periods interacting with a database.
It often also happens that data can be modified directly in the database, not via the application. I agree this is not best practice but in larger enterprises it happens.
Once you start implementing foreign keys in the client you are adding a lot of complexity to a problem that most mature relational database system do well enough to be trusted by a lot of traditional financial institutions.
Re:MySQL Bug #11472: Triggers not executed following foreign...
by delinka on Sunday June 21, 2015 @06:17PM
(#9755217)
These kinds of access are not proper discipline. And a lack of proper discipline is the reason to enforce constraints in production. As for "adding complexity to client apps" - how could you possibly create an app to interact with a database and not know about the structure of the database? This is beyond irresponsible. At the least, should an institution desire to allow third-party access to their database, then that access should be provided through a gateway with the intelligence to enforce constraints without relying on the database server.
Re:MySQL Bug #11472: Triggers not executed following foreign...
by needusername on Monday June 22, 2015 @01:27AM
(#9756332)
> It often also happens that data can be modified directly in the database, not via the application.
Yup, 3rd level support "winging it" in production.
And also migration scripts.
Re:MySQL Bug #11472: Triggers not executed following foreign...
by contingencies on Sunday June 21, 2015 @04:19PM
(#9754847)
Great tale! Much respect to the development and operations teams here. Out of interest, what kind of purpose was the database?
Yes, foreign keys were essential to data correctness. No, they were not being enforced in production. Stew on that just a bit. Not enforced in production.
During client application development, development databases enforced the constraint. Any errors resulting in foreign key mismatches disqualified the client app for release. The applications were required to be aware of the constraint and to learn to work within it. Production gained the speed benefits of not having to enforce the foreign keys.
You can indeed eat your cake and have it, too, iff you are disciplined.