A properly written application layer also prevents data loss from stupid mistakes. A stupid mistake made while setting up a properly normalized database also causes data loss.
You have to be very smart to be able to design a normalized constrained DB well. The fact that only smart people can do it doesn't mean that people who don't do it aren't smart.
Agreed but the application layer _generally_ doesn't have the abstractions at a point where it's trivially easy to put these safe guards in place like it is at the database layer.
Also, "stupid mistakes" does not in any way imply that the people who make them are stupid. Nor am I implying that you have to be particularly intelligent to normalize and constrain a database properly. I'm simply lamenting how undervalued a "good schema" can be.
[side note: upvoted your comment :D]
Eh, nit picking, but I think "very smart" is overkill--I think "just smart" people should still be capable of designing normalized constrained schemas.
If they are incapable of doing this, then I don't want them writing any code anyway.
Being capable of doing it and choosing not to is either at least forgivable or completely understandable, depending on the situation.
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.
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.