It is possible to move foreign keys and other constraints and consistency checks into the application. But that comes at a huge cost: Either you need to be generous with your use of transactions to ensure that no invalid state is ever written, and your application has to know and use those transactions. Which is something application developers in my experience often fail at. Or you have to accept that broken inconsistent data is a fact of life that can happen at any time, and will increase in times of high throughput and concurrency (where the business part of the company will care most about things running smoothly). Also, you will limit yourself to exactly one application using the database, because otherwise you will have to duplicate your constraint logic in any application precisely.

There is also a cost of doing constraints and consistency in SQL, the language is somewhat limited in what you can easily represent (anything is possible, but only some things are easy). And the handling of violations still needs to be implemented in the application, which often necessitates at least some duplication and fiddling to e.g. get friendly and useful error messages.

So imho, yes, SQL has its problems, but it is still the lesser evil in almost all cases.

> Or you have to accept that broken inconsistent data is a fact of life that can happen at any time

You have to accept this anyway. Data can be broken and inconsistent in so many different ways that you cannot protect against every possible way. People will type the wrong thing in sometimes, in ways you never expected. Someone pasting text into the wrong field is just as "broken and inconsistent" as an ID field pointing to a nonexistent entity. How important those errors are to the business depends on how important those columns are, not whether they're an ID vs. text. And as another person pointed out, data always ends up split/replicated among multiple systems anyway.

> Also, you will limit yourself to exactly one application using the database, because otherwise you will have to duplicate your constraint logic in any application precisely.

Layer your architecture? Expose an API? Microservices? There are all sorts of ways around this problem.

Yes, it makes it harder for people to just log into your database and run their own SQL queries. Sometimes that may be politically hard to sell. But I heard this same argument in college when private variables were introduced: wait, but, what if someone wants to directly manipulate private variables of class members? That is not a virtue. It's perfectly reasonable to assert that some internals are simply private implementation details. You are reserving your right to change those details in the future as the application changes. You are separating the details of your persistence layer from the public interface that you're exposing. That is a virtue.

I am not sure what your point is, as you have not yet described it fully. Do you mean to suggest that you just drop the C of ACID? What is there to gain exactly besides the freedom that is like the freedom of a blind man crossing the freeway.

How do you regain the C from the application layer? Do you reject scaling horizontally? Or does the C just interfere with achieving the mellow developer nirvana.

It's inevitable that some data will be split between more than one service / database. I'm not saying we should have data whimsically split up, but foreign key constraint logic must eventually come to live in the app layer too.