Well, yes - that’s ISO SQL “Assertions” right there.
I appreciate that eschewing RDBMS-provided data-integrity features makes sense when it makes sense to - especially when it’s your application code, and yours alone, that guards access to the underlying store.
But such-as-it-is, my current industry (think: incredibly unappealing on-prem CRMs) is all about treating the RDBMS as canon; reason 1 is because there’s a dozen companies all offering value-add services and systems that all integrate into each other solely by having an on-prem agent daemon that just logs-in to the first Oracle box it sees on the LAN and execs its raw SQL, not just SELECT, but plenty of UPDATE and ALTER - all without the decency of a TRANSACTION.
So having even only the most rudimentary of FK and CHECK constraints is what keeps hundreds of thousands of small-to-medium-sized business from imploding from a hosed production DB. They pay for themselves.
…but ifs not enough. There’s still plenty of data-anomalies even in the most well-designed aspirational 6th Normal Form DB you could make today simply because the RDBMSs are awkwardly rigid and inflexible in that regard. I wouldn’t be complaining about the lack of (cheap, high-perf!) EXISTS/NOT-EXISTS constraints in MSSQL/Oracle/MariaDb if those RDBMS were feasibly extendible in that regard. Postgres is the exception here, but unfortunately for me absolutely zero of the systems I work with use it; instead they’re more likely to run on Progress AS/400 or some proprietary xBase derivative.