I recently read in Fowler's PofEA that some database engines allow you to defer RI checks until the end of the transaction. Is this possible in SQL Server?
我最近在Fowler的PofEA中读到,一些数据库引擎允许您将RI检查推迟到事务结束。这可能在SQL Server中吗?
The problem comes with some scenarios where I am trying to save changes that include insertions and deletions, it can be tricky to determine the correct order in which to perform the operations to avoid RI failures. I know that at the end of the transaction the RI will be good so it would seem ideal to defer these checks.
问题出现在我尝试保存包含插入和删除的更改的某些情况下,确定执行操作以避免RI失败的正确顺序可能很棘手。我知道在交易结束时RI会很好,所以推迟这些检查似乎是理想的。
4 个解决方案
#1
3
Looks like sql server doesn't allow this, but you aren't the only one who wants it. It's part of the SQL 92 standard, so they've had 17 years now to figure it out. Then again, MySQL doesn't support it either (although Oracle does).
看起来sql server不允许这样,但你并不是唯一想要它的人。它是SQL 92标准的一部分,所以他们已经有17年了解它。然后,MySQL也不支持它(尽管Oracle确实如此)。
Reordering the statements is probably your best option.
重新排序这些陈述可能是您的最佳选择。
#2
2
You would be better off determining the correct order so that the transaction can fail at the appropriate time. This should be a one-time determination.
您最好确定正确的订单,以便交易在适当的时候失败。这应该是一次性的决定。
#3
2
SQL Server does not have this feature.
SQL Server没有此功能。
May I humbly suggest that if it's tricky to determine the correct insertion order (or there is not one), that your database design may need to be refactored or at least examined very carefully.
我可以谦卑地建议,如果确定正确的插入顺序(或者没有正确的插入顺序)很棘手,那么您的数据库设计可能需要重构或至少要仔细检查。
#4
2
If you are on SQL Server 2008, you can use MERGE to combine insert/delete into a single RI-safe transaction...
如果您使用的是SQL Server 2008,则可以使用MERGE将插入/删除组合到单个RI安全事务中...
#1
3
Looks like sql server doesn't allow this, but you aren't the only one who wants it. It's part of the SQL 92 standard, so they've had 17 years now to figure it out. Then again, MySQL doesn't support it either (although Oracle does).
看起来sql server不允许这样,但你并不是唯一想要它的人。它是SQL 92标准的一部分,所以他们已经有17年了解它。然后,MySQL也不支持它(尽管Oracle确实如此)。
Reordering the statements is probably your best option.
重新排序这些陈述可能是您的最佳选择。
#2
2
You would be better off determining the correct order so that the transaction can fail at the appropriate time. This should be a one-time determination.
您最好确定正确的订单,以便交易在适当的时候失败。这应该是一次性的决定。
#3
2
SQL Server does not have this feature.
SQL Server没有此功能。
May I humbly suggest that if it's tricky to determine the correct insertion order (or there is not one), that your database design may need to be refactored or at least examined very carefully.
我可以谦卑地建议,如果确定正确的插入顺序(或者没有正确的插入顺序)很棘手,那么您的数据库设计可能需要重构或至少要仔细检查。
#4
2
If you are on SQL Server 2008, you can use MERGE to combine insert/delete into a single RI-safe transaction...
如果您使用的是SQL Server 2008,则可以使用MERGE将插入/删除组合到单个RI安全事务中...