Even when using ACID compliant databases, Consistency check should be replicated in your application layer, atleast for mission critical data. But maybe that's just me.
I am genuinely sad that such a statement would ever be uttered. "Even when"? Why would you choose to use a database that isn't reliable?
>Consistency check should be replicated in your application layer, atleast for mission critical data.
Maybe if there were some way to do that, but there isn't. It doesn't get replicated, it gets re-implemented, which is quite different. And it results in application code in app X that says data is bad while the DB says it is ok, and then application Y also says it is ok and inserts it fine, and then app X pukes getting it back out later, etc. Leave the DB in charge of the rules and when it gives you an error, you can go ahead and relay that error to the user (in a nicer format one would assume).