[delinka]

I've experienced several highly-performant, correct databases with concurrent clients from around the globe ... and they had no enforcement of foreign keys.

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.

[ThinkBeat]

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.

[delinka]

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.

[needusername]

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

[contingencies]

Great tale! Much respect to the development and operations teams here. Out of interest, what kind of purpose was the database?

[delinka]

It was in a financial institution. It held every transaction in the financial enterprise.

[spotman]

Nicely put. 100% agreed.