"You have to be very smart to be able to design a normalized constrained DB well."
But you can be a complete moron and write "[a] properly written application layer [that] prevents data loss from stupid mistakes"?
What's the difference? Writing correct code can be hard. I don't think it's particularly easier to apply all your constraint in app code unless you just don't know about the database backend you're using.
It's easier to constrain your objects in the same language they're written in. Say I have an object where my constraint is that either fielda is set, or fieldb and fieldc are set, but not both (ignoring for the moment that that's a stupid object to have). I can trivially enforce that in a constructor, but it would take me quite a while to work out how to express that in SQL, if it's even possible.
This is fairly trivial to express as a table-level constraint. I've done very similar things in Postgres. I have no idea if you can do this in MySQL, but it's quite crippled.
But you can be a complete moron and write "[a] properly written application layer [that] prevents data loss from stupid mistakes"?
What's the difference? Writing correct code can be hard. I don't think it's particularly easier to apply all your constraint in app code unless you just don't know about the database backend you're using.