> CHECK constraint violated is no good for humans.
Well, sure, an application should respond to DB errors by presenting appropriate messages on the UI, just like any other errors it encounters. You should only see "CHECK constraint violated" if you are bypassing the app and using the DB. Otherwise, you should see something nice provided by the app.
> Why shouldn't I enforce unique constraints in the application?
Because you should do it in the database whether or not you do it in the application, and then once you have, well, DRY.
Well, sure, an application should respond to DB errors by presenting appropriate messages on the UI, just like any other errors it encounters. You should only see "CHECK constraint violated" if you are bypassing the app and using the DB. Otherwise, you should see something nice provided by the app.
> Why shouldn't I enforce unique constraints in the application?
Because you should do it in the database whether or not you do it in the application, and then once you have, well, DRY.