The problem is that sometimes you simply can't do it properly in application. Uniqueness is the most obvious example, although not the only one.

I probably phrased that incorrectly. I definitely think that you should have validation logic at the database level. I've had a couple of major issues in the past (while still learning web dev) where lack of indexes/constraints has pretty much ruined my database when I tried use them in production

You also want to implement foreign keys in the database since you do not want to have to remember to lock referenced rows when inserting new referring rows. The database helps you out with concurrency issues.