Yeah, I guess it depends on how good your constraints engine is versus what your constraints actually are. If your actual constraints can't be modeled, or if you choose not to model them at the database layer (e.g. for performance reasons) then you have to implement the constraints somewhere else.
I worked on a database that allowed (almost) every table to mark a row as deleted with a boolean-type column. This caused problems when you wanted to create a new row that had the same values for the table's key-columns as a deleted row. You can't just add the deleted column to the key and have all the functionality you want (multiple deleted rows with the same values in the table's key-columns). Each table could use (or not) the flag in different ways and there was no simple way to enforce consistent behavior across all tables. The constraint has to be placed somewhere else, either in explicit code, or by creating new abstractions inside the database that allow you to represent the actual constraints of your application.
I worked on a database that allowed (almost) every table to mark a row as deleted with a boolean-type column. This caused problems when you wanted to create a new row that had the same values for the table's key-columns as a deleted row. You can't just add the deleted column to the key and have all the functionality you want (multiple deleted rows with the same values in the table's key-columns). Each table could use (or not) the flag in different ways and there was no simple way to enforce consistent behavior across all tables. The constraint has to be placed somewhere else, either in explicit code, or by creating new abstractions inside the database that allow you to represent the actual constraints of your application.