They're limited in what they can express; your application often has invariants you want to enforce/maintain that can't be (performantly) expressed with DB constraints, and must be validated another way.
As great as it can be to enforce rules within the database, a lot of them usually end up needing to be enforced at the application layer instead. Especially when performance at scale comes into play.
I think it’s a balance. Transactions + Constraints can enforce most things but there will certainly be things that can only be verified in the app.
My goal is always to verify what I can in the database to minimize potential data cleanup. In my experience, app only verification always leads to future time investments to clean up the mess.
DB constraints can verify an important but inherently limited, simplified subset of data integrity.
For a crude example, it's trivial for DB constraints verify (via a foreign key constraint) that all your contracts belong to some customer, but very difficult for DB constraints to verify that all your currently active contracts belong to a currently active customer, even if the definition of 'active' is some relatively simple business logic.
So in my experience it's not that rare to have some code-based data integrity tests that run various sanity checks on production data to verify things that DB constraints can not.
Depends on the database, sometimes the database config, as to whether they'll actually be enforced or not, or in what situations data might evade enforcement of the constraints…
Applies to vendors, too. Had some data in Rackspace Files where "list files" would say X existed, but "GET X" got you a 404. Had an AWS RDS instance; query on it returned no results. Adding the "don't use the index" index hint caused it to return data. (Allegedly, this bug was fixed, but we had migrated off by that point, so I never got to confirm it.)
Conversely, I do like DB constraints, because if the DB constraint doesn't exist, then I guarantee you the production DB has a row that is a counter-example to whatever constraint you think the data should obey…
> Had some data in Rackspace Files where "list files" would say X existed, but "GET X" got you a 404.
Well yes, Rackspace Files (aka OpenStack Swift) is eventually consistent. It says so literally in the first sentence of the documentation [1]. But this discussion is about relational databases with ACID guarantees, where the C is literally "consistent".
