在 sql server 中寻找用户友好的 CheckConstraint 错误消息策略
我正在设置一个数据库，有几个表，其中有许多行间依赖关系，需要在创建/更新行之前进行检查。有许多字段具有来自多个来源的 max/min/avg/stdev。
我想我会创建一些约束来确保所有关系的 max>=min、avg>=min、avg<=max、stdev>=0 。通过这种方式，我在数据库中设置了它，任何错误地接触数据的东西都会引发错误。这种机制工作得很好……除了从用户的角度来看错误消息很可怕之外，基本上是说一个约束失败了，并让用户来确定 20 个约束中哪一个是坏的。
我可以通过查看约束异常然后运行数据来查找问题来在客户端代码中修复此问题。该解决方案在两个地方进行验证...
我不能如愿地将错误消息分配给每个约束（在数据库中）并通过异常机制将该消息过滤到 UI。是否有某种机制可以让用户友好的消息渗透到 UI，而无需在业务逻辑中复制数据验证？更重要的是，解决这类问题的基本策略是什么？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以控制的从服务器返回的唯一内容是约束名称 - 因此请尝试确保这些名称尽可能具有描述性，或者将约束名称映射到异常处理程序中的用户友好的错误消息。
在这种情况下，请尝试使每个约束尽可能细化，因此每种类型的故障都是不同约束的故障。当然，您还会遇到这样的问题：SQL Server 只会报告第一个失败的约束 - 您的数据实际上可能会失败多个约束。
除了在提交数据之前在应用程序中重复检查之外，我想不出您还能做什么。我倾向于认为这种重复类似于网页上的客户端/服务器验证 - 您在客户端运行验证，以改善用户体验，并避免不必要的服务器往返。但如果客户端验证因任何原因失败，服务器必须保护自己。
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.