逻辑：数据库或应用程序/ 2（约束检查） [英] Logic: Database or Application/2 (constraints check)

问题描述

这是此问题的特定版本。
我想检查是否插入一个重复的行。我应该在我的应用程序层中以编程方式检查它：

  if（exists（obj））
 {
 throw new DuplicateObjectException（）; 
} 
 HibernateSessionFactory.getSession（）。save（obj）; 
  

或者我应该捕获数据库层抛出的异常并在违反约束时触发？ p>

  try 
 {
 HibernateSessionFactory.getSession（）。save（obj）; 
} 
 catch（ConstraintViolationException e）
 {
 throw new DuplicateObjectException（）; 
} 
  

EDIT：换句话说：是否存在（这是好的数据库设计反正，我不能确定我的应用程序将是唯一的访问表）我将依赖约束和处理异常，其违例将提高，或者我最好检查

EDIT2：当然，我在一个事务中执行check + insert，锁定表以确保没有其他进程正在写另一个记录

这个唯一性是正确的 - 没有问题。

由于约束存在，应该在应用程序中编写哪种方法？我的首选是尝试插入和捕获异常。因为大多数插入都会成功，所以只有少数插入会失败，因为重复（这就是异常的含义！）：在每次插入之前执行一个存在检查是无效的，当数据库要执行自己的约束检查。

此外，理论上，exists检查有可能是错误的 - 如果有人设法提交一个记录与相同的键值在您的exists检查和插入。然后，如果你不捕获数据库异常，你会相信插入成功，但事实上它没有。

This is a specific version of this question.
I want to check if I am inserting a duplicate row. Should I check it programmatically in my application layer:

if (exists(obj))
{
    throw new DuplicateObjectException();
}
HibernateSessionFactory.getSession().save(obj);

or should I catch the exception thrown by the database layer and triggered when I violate the contraint?

try
{
    HibernateSessionFactory.getSession().save(obj);
}
catch(ConstraintViolationException e)
{
    throw new DuplicateObjectException();
}

EDIT: In other words: though the constraint is there to remain (it's good database design anyway, and I can't be sure my app will be the only one accessing the table) shall I rely on the constraint and handle the exception its violation will raise, or I'd better check anyway?

EDIT2: Of course I do check+insert within a transaction, locking the table to ensure no other process is writing another record in the meantime

解决方案

First, you must have a primary key or unique constraint on the database to enforce this uniqueness properly - no question.

Given that the constraint exists, which way should you code in the application? My preference would be to try the insert and catch the exceptions. Because presumably most inserts will succeed, only a few will fails as duplicates (that's what "exception" implies!): it is inefficient to perform an exists check before every insert, when the database is going to be performing its own constraint checking anyway.

Also, it is theoretically possible for the exists check to be wrong anyway - if someone else manages to commit a record with the same key value in the small interval between your exists check and your insert. Then, if you don't trap the database exception, you will believe the insert succeeded when in fact it didn't.

这篇关于逻辑：数据库或应用程序/ 2（约束检查）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！