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.
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.
Yup, 3rd level support "winging it" in production.
And also migration scripts.
Facebooks mysql architecture (at least used to, likely still is) based on this: https://backchannel.org/blog/friendfeed-schemaless-mysql
Note the simple schema, and lack of FKs.
FKs are rarely present in extremely high transactions per second systems that operate on the same few tables. This is due to index contention and locking.
I say this with lots of experience working on different production systems that see hundreds of thousands of transactions per second. I have yet to see one arrive at these kind of numbers using foreign keys, unless it's something like a giant shared hosting platform, that is operating on hundreds and hundreds of different tables. (therefore, less index contention)
Often, in these shops, data correctness is not validated in realtime in this way, but often in a way that is not in the critical path of answering queries. (more like, eventually consistent). Some places have entire teams for this.
To your point on correctness, my experience ranges a lot in areas where correctness can be eventually consistent. For something like healthcare or banking, you would rather spend the money on way more hardware, because you can't afford correctness to be off. However, with things like globally scaled social apps, this is just not the case.
Most people tend to still use Oracle in those situations. Which is legions slower , and legions more expensive than mysql or pgsql.
"Eventually consistent"? Explain. (Do you mean in an ACID transaction?)
In things like a large social media app where the reality is no FKs, and 100k queries-per-second, for instance, you would write into your application logic how to deal with child rows and parent rows, and not rely on FKs to raise an exception, or perform cascading deletes and things.
So if ( when ) there is either a bug, hiccup, or a variety of other reasons that can as you mention make things question correctness (or specifically: referential integrity, if we are going to nitpick), these are usually cleaned up out of band.
So you may have a table you have to slowly iterate through later and remove rows in which their parent rows no longer exist anymore, as an example.
In something like the friend feed schema used in the previous example, read up on how that works, as you will see they sort of turn SQL on its head a bit, to make it more flexible and deal with these shortcomings.
