Even if it was easy to get things right at the application layer, in a legacy system (and today's modern hotness is tomorrow's legacy system), the database is a constant.
Entire generations of application may rise and fall. New languages, frameworks, developers all lead to rot over time. Heck, some legacy projects the application code is incomplete or lost.
But the database doesn't rot. Show me a database that is 20 years old, and it is as fresh as the day it was created. If it has FK constraints, it's even healthier.
That means that the more value is embedded in the database - FK and of course many more constraints - that value will live for decades.
I have lived the rise and fall of all those things in my own projects (rewrites, you name it) using other's ORMs (for example). Handling as much as you reasonably can at the data layer is definitely the way to go.
Commenting just to draw attention to your comment.
Entire generations of application may rise and fall. New languages, frameworks, developers all lead to rot over time. Heck, some legacy projects the application code is incomplete or lost.
But the database doesn't rot. Show me a database that is 20 years old, and it is as fresh as the day it was created. If it has FK constraints, it's even healthier.
That means that the more value is embedded in the database - FK and of course many more constraints - that value will live for decades.