Constraints. Database versus application

When you talk about data constraints there's always a debate about where to put them : database versus application.
I noticed that some programmers go with the application (especially the O/RM guys) while the more "database savy" insist on placing them on database. While i'd go with the application approach there are some ups and downs that must be considered before taking a decision :
- the upside of putting them at the app layer is that they are easier to change/extend and they're database independent.
- the downside is that sometimes data integrity can be compromised. For instance if somebody decides to implement a check constraint at the application level in a very high traffic application there is the possibility for the following sequence to happen : checks for value X in a certain column from 2 application instances which succedes. Now if both users enter the same value the data integrity is compromised.
But, keeping the same check constraint example, also note that this unoque constraints can't be implemented at the database level either (you can't put a unique constraint on nvarchar(4000) field in Sql Server 2005 for instance because the index size would be too big).
So make sure you're aware of the limitations of each side before you implement the solution.