UNIQUE约束与INSERT之前的检查 [英] UNIQUE constraint vs checking before INSERT

问题描述

我有一个带有列-ID，Property，Property_Value的SQL Server表RealEstate.该表大约有5到1千万行，并且将来还会增加.我只想在此表中不存在Id，Property，Property_Value的组合时插入行.

I have a SQL server table RealEstate with columns - Id, Property, Property_Value. This table has about 5-10 million rows and can increase even more in the future. I want to insert a row only if a combination of Id, Property, Property_Value does not exist in this table.

示例表-

1,Rooms,5
1,Bath,2
1,Address,New York
2,Rooms,2
2,Bath,1
2,Address,Miami

不允许插入2,Address,Miami.但是，2,Price,2billion可以.我很想知道哪种方法是最好的方法，并且为什么.为什么这部分对我来说最重要.两种检查方式是-

Inserting 2,Address,Miami should NOT be allowed. But, 2,Price,2billion is okay. I am curious to know which is the "best" way to do this and why. The why part is most important to me. The two ways of checking are -

1. 在应用程序级别-应用程序应在插入行之前检查行是否存在.
2. 在数据库级别-在所有3列上设置唯一约束，并让数据库 而不是人员/应用程序进行检查.

1. At application level - The app should check if a row exists before it inserts a row.
2. At database level - Set unique constraints on all 3 columns and let the database do the checking instead of person/app.

有没有一种方案比另一种更好?

Is there any scenario where one would be better than the other ?

谢谢.

PS:我知道已经有一个类似的问题，但是它不能回答我的问题- 唯一约束与预检查 另外，我认为UNIQUE适用于所有数据库，因此我不认为应该删除mysql和oracle标记.

PS: I know there is a similar question already, but it does not answer my problem - Unique constraint vs pre checking Also, I think that UNIQUE is applicable to all databases, so I don't think I should remove the mysql and oracle tags.

推荐答案

我认为大多数情况下，两者之间的差异会很小，因此选择主要应通过选择最终易于理解的实现来实现给第一次看代码的人.

I think it most cases the differences between that two are going to be small enough that the choice should mostly be driven by picking the implementation that ends up being most understandable to someone looking at the code for the first time.

但是，我认为异常处理具有一些 small 优点:

However, I think exception handling has a few small advantages:

• 异常处理避免了潜在的竞争状况.如果另一个过程在您的支票和插入内容之间插入了一条记录，则检查然后插入"方法可能会失败.因此，即使您正在执行先检查然后插入"操作，您仍然希望在插入操作上进行异常处理，并且如果您已经在进行异常处理，那么您也可以取消初始检查.

• Exception handling avoids a potential race condition. The 'check, then insert' method might fail if another process inserts a record between your check and your insert. So, even if you're doing 'check then insert' you still want exception handling on the insert and if you're already doing exception handling anyways then you might as well do away with the initial check.

如果您的代码不是存储过程，并且必须通过网络与数据库进行交互(即应用程序和数据库不在同一个盒子上)，那么您要避免进行两个单独的网络调用(一个用于检查，另一个用于插入)，并通过异常处理进行操作，这提供了一种通过单个网络调用处理整个事件的简单方法.现在，有很多方法可以执行检查然后插入"方法，同时又避免了第二次网络调用，但是简单地捕获异常可能是解决该问题的最简单方法.

If your code is not a stored procedure and has to interact with the database via the network (i.e. the application and the db are not on the same box), then you want to avoid having two separate network calls (one for the check and the other for the insert) and doing it via exception handling provides a straightforward way of handling the whole thing with a single network call. Now, there are tons of ways to do the 'check then insert' method while still avoiding the second network call, but simply catching the exception is likely to be the simplest way to go about it.

另一方面，异常处理需要一个唯一的约束(这实际上是一个唯一的索引)，这需要进行性能折衷:

On the other hand, exception handling requires a unique constraint (which is really a unique index), which comes with a performance tradeoff:

• 在非常大的表上创建唯一约束会很慢，并且会对该表的每次插入都会造成性能下降.在真正的大型数据库上，您还必须预算用于执行约束的唯一索引所消耗的额外磁盘空间.
• 另一方面，如果您的查询可以利用该索引，则可以更快地从表中进行选择.

我还要注意，如果您实际要执行的操作是更新否则插入"(即，如果具有唯一值的记录已经存在，那么您想更新该记录，否则插入新记录)，那么您实际要使用的是特定数据库的UPSERT方法(如果有).对于SQL Server和Oracle，这将是MERGE语句.

I'd also note that if you're in a situation where what you actually want to do is 'update else insert' (i.e. if a record with the unique value already exists then you want to update that record, else you insert a new record) then what you actually want to use is your particular database's UPSERT method, if it has one. For SQL Server and Oracle, this would be a MERGE statement.

这篇关于UNIQUE约束与INSERT之前的检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！