I'm setting up a database and there are several tables where there are many inter-row dependencies that need to be checked prior to creating/updating a row. There are many fields that have max/min/avg/stdev from many sources.
I figured that I would create some constraints that made sure max>=min, avg>=min, avg<=max, stdev>=0 for all of the relationships. This way I set it up in the DB and anything that touches the data incorrectly throws an error. This mechanism works fine...other than the fact that the error messages are horrible from the user's perspective, basically saying that a constraint failed and leaving it to the user to determine which one of the 20 constraints was bad.
I can fix this in the client code by seeing the constraint exception and then running through the data to find the problem. That solution has validation happening in two places...
I can't have my wish of assigning an error message to each constraint (in the db) and having that message filter up to the UI via the exception mechanism. Is there some mechanism to have user friendly messages percolate up to the UI without replicating the data validation in the business logic? More importantly what is the basic strategy for this type of problem?
The only thing that comes back from the server that you have control over is the constraint name - so try to ensure that these are as descriptive as possible, or map constraint names to user friendly error messages in your exception handler.
In this situation, try to make each constraint as granular as possibly, so each type of failure is a failure of a different constraint. Of course, you also have the problem that SQL Server will only report the first constraint that failed - your data may actually fail multiple constraints.
I can't think of anything else you could do, other than duplicating the checks in your applications, before they submit the data. I tend to think of this kind of duplication as similar to client/server validation on web pages - you run validation on the client side, to improve the user experience, and avoid unnecessary round trips to the server. But the server has to protect itself if the client validation fails for any reason.