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