I disagree with the recommendation about every layer, all the time. It sounds too dogmatic to me.
All design represents a choice between functionality and cost. Your validation choices should reflect that as well.
I think your decisions should take into account the sharing and reuse of layers.
If the database is shared by more than one application, then the database cannot depend on every application validating properly. In that case, the database must validate to protect itself.
In this day and age of SQL injection attacks, I think binding and validating prior to reaching the persistence tier is a must. The schema should do what it must to ensure referential and business integrity (e.g. unique constraints on columns and required "not null"), but other validations should be done prior to hitting the database.
If the database is wholly owned by one and only one application, and there's a service that is the only gateway into the data, then validation can be done by the service. The cost of duplicating the validation on the database layer can be waived.
Likewise between the UI and the service layer.
Double validation on client and service tiers is common because the service is shared by many clients in a service oriented architecture. Today your service might be used by a browser based UI; suddenly there's a flock of mobile apps along side it that clamor for services, too. In that case the service absolutely must validate and bind each and every request.
No manufacturer polishes every surface to mirror quality. Sometimes a rough cast surface is permissible, because the benefit of grinding and polishing is negligible and the cost is too great.
Same with software.
I don't like dogmatic statements. It's better to understand the implications of your choices. Know the rules and when it's appropriate to break them.