I care about these things specifically because the IRS doesn't tax it. I save about 6k per year because my startup feeds me 5 lunches/3 dinners per week and pays for my public transportation. That's probably ~8k pre-tax dollars.
SQL constraints are a given; always use them. However, it's best practice to also validate your data before it gets to the database for several reasons.
A) Depending on a database check constraint failure is analogous to using a try/catch when a pre-emptive check would work; it's dirty.
B) You're putting unnecessary load on the hardest system to scale.
C) Users/systems will get instant feedback with a client-side validation. Depending on the data, this could be an enormous bandwidth savings.
I am not sure I would say "best practice" but I would rank it as "often useful." I'll explain.
I disagree with your characterization of using exceptions when a check condition behave identically. It's not dirty, it's exception oriented.
I disagree with your characterization of the database as the hardest system to scale. What's hard is fixing a performance problem in the database after an organization has effectively put the schema in a cast by developing 30 apps/scripts directly coupled to the core of the (broken!) schema.
I DO however agree that client-side feedback is important. Validation on the client might include a lot of data used for prompting, or perform validation only the user-interactive application can do. This exceeds the role of the data constraints you might implement in your database for obvious reasons.
I'm not sure Q does more than duplicate the constraints I would put in my database to start with. There's a lot more to collecting input than enforcing closed-form rules.
"exception oriented" programming is dirty. It's especially filthy when your expecting exceptions from a database for these reasons
a) Returning errors to the user is asking for a slipup handling the different exceptions, thus returning more info than desired. Cue the injection hacks!
b) Depending on the DBMS and its configuration, an upsert destined to fail may block reads and other writes from executing!
I had a feeling somebody would gripe about duplicated validation rules; it's a problem that needs an elogant solution on your own stack.
In real world situations you already need to go to the database to do validation. For example to check that a username is not already taken. And because of the race condition, you still need to be prepared to handle a duplicate-key integrity error when actually trying to create the user row.
