是否可以将参照完整性检查推迟到 SQL Server 中的事务结束？
我最近在 Fowler 的 PofEA 中读到，某些数据库引擎允许您将 RI 检查推迟到事务结束。这在 SQL Server 中可能吗？
在某些情况下，我试图保存包括插入和删除在内的更改，因此会出现问题，确定执行操作的正确顺序以避免 RI 失败可能很棘手。我知道在交易结束时 RI 会很好，因此推迟这些检查似乎是理想的选择。
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?
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
看起来 sql server 不允许这样做，但您 不是只有一个想要它。它是 SQL 92 标准的一部分，因此他们已经花了 17 年的时间来解决它。话又说回来，MySQL 也不支持它（尽管 Oracle 支持）。
重新排序语句可能是您最好的选择。
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).
Reordering the statements is probably your best option.
您最好确定正确的顺序，以便事务可以在适当的时间失败。这应该是一次性的决定。
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.
SQL Server没有这个功能。
我可以谦虚地建议，如果确定正确的插入顺序很棘手（或者没有），那么您的数据库设计可能需要重构或至少非常仔细地检查。
SQL Server does not have this feature.
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.
如果您使用的是 SQL Server 2008，则可以使用 MERGE 来组合插入/删除到单个 RI 安全事务中...
If you are on SQL Server 2008, you can use MERGE to combine insert/delete into a single RI-safe transaction...