我应该检查代码中的数据库约束还是应该捕获数据库抛出的异常
我有一个应用程序将数据保存到名为“作业”的表中。 Jobs 表有一个名为 Name 的列，该列具有 UNIQUE 约束。 名称列不是主键。 我想知道在尝试保存/更新新条目之前是否应该自己检查重复条目，或者是否最好等待数据访问层引发的异常。 如果这个应用程序有任何重要性，我将使用 NHibernate
感谢大家的宝贵意见。
我发现了另一个原因，为什么我应该在代码中进行验证，而不仅仅是等待抛出异常（并被我的代码捕获）。 看来 NHibernate 只会抛出 NHibernate.Exceptions.GenericADOException ，在这种情况下，它对于异常原因的信息不是很多。 或者我在这里错过了 NHibernate 的某个方面？
I have an application that saves data into a table called Jobs. The Jobs table has a column called Name which has a UNIQUE constraint. The Name column is not PRIMARY KEY. I wonder if I should check for duplicate entries myself before I try to save/update a new entry or if it's better to wait for an exception thrown by the data access layer. I'm using NHibernate for this App if it's of any importance
Thanks to everybody for the great input.
I have found one more reason why I should validate in code and not just wait for an exception being thrown (and caught by my code). It seems that NHibernate will only throw an NHibernate.Exceptions.GenericADOException which is not very informative regarding the cause of the exception in this case. Or am I missing an aspect of NHibernate here?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
答案是：两者都有。
如果您的数据库有约束，它可以保证数据的某些不变量，例如唯一性。 这在几个方面都有帮助：
如果您的
申请，违反
约束将标记一些东西
否则可能不会被注意到。
数据库的其他用户可以
对某人的行为做出更多假设
DBMS 强制执行的数据
不变量。
数据库会保护自己免受
不正确的更新违反了
限制。 如果你发现你还有其他的
系统或接口填充
数据库沿着轨道，
由数据库强制执行的约束
意味着任何被捕获的东西
约束不会（或至少
不太可能）破坏您的系统。
除了最微不足道的情况外，应用程序和数据库都处于 M:M 关系中。 应用程序仍应具有适当的数据和业务规则验证，但您仍然不应该计划让您的应用程序成为数据的唯一客户。 在数据仓库领域工作几年，您就会看到具有这种心态的人设计的应用程序的效果。
The answer is: both.
If your database has constraints it can guarantee certain invariants about the data, such as uniqueness. This helps in several ways:
If you have a bug in your
application, violating the
constraint will flag something that
might otherwise not be noticed.
Other users of the database can
assume more about the behaviour of
the data as the DBMS enforces
invariants.
The database protects itself from
incorrect updates that violate the
constraints. If you find you have some other
system or interface populating the
database down the track, the
constraints enforced by the database
mean that anything caught by the
constraints won't (or at least
is less likely to) break your system.
Applications and databases live in a M:M relationship in any but the most trivial cases. The application should still have the appropriate data and business rule validations but you should still not plan for your application being the only customer of the data. Work in data warehousing for a few years and you'll see the effects of applications designed by people with this mindset.
如果您的设计很好（数据库和 BL），那么数据库不应该有任何 BL 中不会处理的约束 - 即您不应该向数据库提供不一致的数据。 但没有什么是完美的。
我发现将数据库限制在数据一致性约束下可以让我处理程序代码中的所有 BL 验证，并且我遇到数据库异常的唯一情况是可以（并且应该）修复的设计和编码错误。
在您的情况下，检查名称的唯一性是数据内容验证，在代码中正确处理。 这可能会捕获最接近委托点的错误，您希望可以调用更友好的 UI 资源，而不会在抽象之间引入不需要的耦合。
If your design is good (both database and BL), the database shouldn't have any constraints that wouldn't be dealt with in the BL - i.e. you shouldn't be presenting the database with inconsistent data. But nothing is perfect.
I've found that confining the database to data consistency constraints lets me handle all BL validation in procedural code, and the only cases where I experience database exceptions are design and coding errors which can (and should be) fixed.
In your case, checking the name for uniqueness is data content validation, properly handled in code. Which presumably catches the error nearest the point of commission, where you hopefully have friendlier UI resources to call on without introducing undesirable coupling between abstractions.
我会把这项工作完全留给数据库； 您的代码应该专注于捕获并正确处理异常。
原因：
高度优化以执行
快速有效地约束
方式。 你将没有时间
也优化您的代码。
未来你不会改变
修改你的代码，或者也许你
只需添加一个新的 catch{}。
如果放弃约束，您
不必触摸您的代码
全部。
I would leave that work entirely to the database; your code should focus on catching and properly handling the exception.
Reasons:
highly optimized to enforce
constraints in a fast and efficient
way. You won't have time to
optimize your code as well.
change in the future, you won't have
to modify your code, or perhaps you
will just have to add a new catch{}.
If a constraint is dropped, you
won't have to touch your code at
all.
如果您要自己检查约束，请在数据访问层中进行。 该层之上的任何内容都不应该了解有关数据库或其约束的任何信息。
在大多数情况下，我会建议将其留给 DAL 来捕获源自数据库的异常。 但在您的具体情况下，我认为我们正在讨论基本的输入验证。 在提交整个表单之前，我会选择对数据库进行名称可用性检查调用。
If you are going to check the constraints yourself, do it in the data access layer. Nothing above that layer should know anything about your database or its constraints.
In most cases I'd say leave it to the DAL to catch DB-originated exceptions. But in your specific case, I think we're talking about basic input validation. I'd opt for a name availability check call to the database, before submitting the whole form.
您绝对应该检查数据访问层抛出的任何异常。 检查是否存在具有相同值的记录的问题是，它要求您锁定表以进行修改，直到插入新记录以防止竞争条件。
通常建议检查异常/错误，即使您之前已经检查过所有内容。 几乎总是有一些事情可能会出错，或者你在代码中没有考虑到但数据库强制执行的事情。
编辑：如果我理解问题正确，那么问题不在于数据库是否应强制执行约束，而在于如何在应用程序代码中处理它。 当然，您应该始终在数据库中设置所有约束，以防止不良数据进入数据库。
You should definitely check for any exception thrown by the data access layer. The problem with checking if there is a record with the same value is, that it requires you to lock the table for modifications until you insert the new record to prevent race conditions.
It is generally advisable to check for exceptions/errors, even if you have checked everything yourself before. There is almost always something that can go wrong or which you haven't considered in your code but is enforced by the database.
Edit: If I understand the question right, it is not about if the constraint should be enforced by the database or not, but how to deal with it in the application code. Of course you should always set up all constraints in the database to prevent bad data entering your database.
您需要回答的问题是：
“我是否需要向用户呈现好的消息”。 示例：已有一个名为 TestJob1 的作业。
如果答案是否，只需捕获错误并显示一条常见消息
如果答案是是，请继续阅读
如果您在插入后发现错误，则没有足够的信息来呈现正确的消息（至少以不可知的数据库方式）
另一方面，可能存在竞争条件，并且您可以同时进行事务尝试插入相同的数据，因此您需要数据库约束
一种效果很好的方法是：
消息
显示常见错误消息
（假设这不会发生太多
频繁地）
The question that you need to answer is:
"Do I need to present the user with nice messages". Example: There is already a Job with the name TestJob1.
If the answer is No, just catch the error and present a common message
If the answer is Yes, keep reading
If you catch the error after the insert there isn't enough information to present the right message (at least in an agnostic DB way)
On the other hand, there can be race conditions and you can have simultaneous transaction trying to insert the same data, therefore you need the DB constraint
An approach that works well is:
message
present a common error message
(assuming this won't happen very
frequently)
就我个人而言，我会抓住这个例外。 它更简单并且需要更少的代码。
Personally I'd catch the exception. It's much simpler and requires much less code.
GenericADOException 的内部异常将告诉您数据库操作失败的原因。 您可以捕获 OracleException / MSSQLException / [InsertCustomExceptionHere] 并处理该消息中的错误。 如果您想将其传递回前端（假设用户是输入重复数据的人），您可能需要首先将其包装在自定义异常中，这样您就不会将前端与数据库耦合。 您确实不想传递 RDBMS 特定的异常。
我不同意在执行插入之前检查数据库的唯一性，两次往返数据库的效率不是很高，而且如果用户流量很大的话，当然也无法扩展。
The inner exception of the GenericADOException will tell you why the database action failed. You can catch the OracleException / MSSQLException / [InsertCustomExceptionHere] and handle the error from that message. If you want to pass this back up to the front end (assuming the user is the one who entered duplicate data) you might want to wrap it in a custom exception first so you don't couple your front end to your database. You don't really want to be passing RDBMS specific exceptions around.
I disagree with checking the db for uniqueness before doing an insert, round tripping to the database twice isn't very efficient and certainly isn't scalable if you have a high volume of user traffic.