The problem there is what if you have two different apps connecting to the same DB. When the DB enforces referential integrity both apps can be certain the data in the DB is correct and they wont be able to mess with it. When the apps have to take on that role each app must implement (i.e. unnecessarily duplicate) the same integrity checking functionality. In a complex environment where you might have different apps using the same NoSQL data store that becomes very difficult to manage.

Even if your app is the only app accessing the database, the code in your app can be changed. When you change the validation criteria, do you reread every row from the database and revalidate it?

When validation criteria are changed on an SQL server, it revalidates all the rows - either immediately, or at the end of the transaction. With the DB doing the validation, the default is that all rows are valid.

Also, do you ever side-step your database abstraction layer to send SQL directly? If you ever insert/update/delete rows that way, perhaps for performance reasons, now you have an extra place in your app to make sure the relevant validations are kept up-to-date.

https://robots.thoughtbot.com/validation-database-constraint...

Relatedly, this article suggests that your app should only be validating user input - so there should be no app validation of fields not set by end users - whilst the database itself should validate what your app gives it.