When a database is just functioning as your (relatively monolithic) application's data store, validation on the app side works well. The app is the primary—maybe only—product.

But if you have multiple apps sharing the same data, or the app is a bunch of different things from many years glued together, etc., then there is no longer "the" application side. You'd have to write validation on multiple application sides, and make sure they all agree. The data is at this point often more important than a single app.

Even with single-app data stores, if you ever have to use the SQL prompt directly (say, to do a one-off change that it doesn't make sense to write an app feature for, or time doesn't permit doing so) it's nice to have all the constraints specified in the database.