SQL Server：如何在提交之前忽略引用完整性？
我有一个将行从一个数据库移动到另一个数据库的过程。由于一些循环外键引用链，我无法从旧数据库中删除行，也无法将它们插入新数据库中。
由于整个操作发生在事务1中，我希望 SQL Server 忽略引用完整性故障，直到我调用 COMMIT TRANSACTION。
例如2：
Table: Turboencabulators Table: Marselvanes
========================= =======================
PK TurboencabulatorID int /-> PK MarselvaneID int
^ MarselvanesID int --/ HasGrammeter bit
| PantametricFan varchar(50)
+------------------------------- TurboencabulatorID int
如果我尝试在新表中插入turboencabulator，则在marselvane不存在的情况下，它将失败。颠倒顺序也有同样的问题。
当尝试删除旧行时，我无法删除其中一行，直到删除另一行。
我尝试过使用 n 阶段系统，其中插入所有行，并将外键约束下的任何列设置为 null。然后我更新所有插入的行，放置正确的缺失值。然后，为了删除源行，我将受 FK 影响的所有列清空，然后删除实际行。3
我真正更喜欢的是执行我的 T-SQL 操作，并且让 SQL Server 在我尝试调用 commit 之前不会告诉我。
注释
1分布式
2人为的假设
3我不再这样做了
i have a process to move rows from one database to another. Because of some circular foreign key reference chains i cannot remove the rows from the old database, nor can i insert them into the new database.
Since the entire operation happens in a transaction1, i want SQL Server to ignore referential integrity failures until i call COMMIT TRANSACTION.
For example2:
Table: Turboencabulators Table: Marselvanes
========================= =======================
PK TurboencabulatorID int /-> PK MarselvaneID int
^ MarselvanesID int --/ HasGrammeter bit
| PantametricFan varchar(50)
+------------------------------- TurboencabulatorID int
If i try to insert the turboencabulator in the new table, it will fail without the marselvane already being there. Reversing the order has the same problem.
When trying to remove the old rows, i cannot delete one until the other is deleted.
i've tried doing an n-phase system, where all rows are inserted with any columns that are under foreign key constraint set to null. Then i update all the inserted rows, placing the proper missing values. Then, in order to delete the source rows, i null off all columns affected by a FK, then delete the actual rows.3
What i'd really prefer is to just do my T-SQL operations, and have SQL Server not tell me until i try to call commit.
Notes
1distributed
2contrived hypothetical
3which i'm not doing anymore
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以在开始之前使用 ...
删除约束检查
，完成后用 ... 重新打开它，
这就是我要做的。
-大学教师
you can use ...
to remove the constraint checking before you begin
and when done turn it back on with ...
that's what i'd do anyway.
-don
想象一下你会如何实现它。
如果外键结果延迟到事务提交，则提交必须执行插入/删除/更新时未发生的所有查找/检查/级联操作。想想 FK 约束的真正含义：您的插入执行计划通过附加操作进行“注释”，以验证和强制执行 FK 约束。如果推迟约束，则查询计划中的附加逻辑必须从执行时刻解除绑定，并推送到某个事务上下文中，以便在提交时执行。突然的提交从简短的“标记事务已在日志中提交”操作转变为执行实际事务期间跳过的所有操作的操作。最糟糕的部分是约束可能会失败，并思考应用程序将如何处理失败？通过在执行插入时强制执行约束，应用程序可以捕获错误并采取纠正措施：它准确地知道失败的原因。但是，如果您将其推迟到提交，您尝试提交并捕获异常，那么现在您需要以某种方式从咳嗽异常中找出失败的原因。想想在这种情况下应用程序开发人员的生活将会多么复杂。
这行不通的第二个原因是你仍然没有解决问题。您的表 A 具有 FK 约束到 B。您开始事务，插入 B，然后插入 A，然后从 A 中删除，然后从 B 中删除，然后提交。所有操作在发生时都满足 FK，数据库在提交时满足 FK。然而，如果您推迟约束检查，它们将在提交时失败！
所以我想说，引用完整性按原样工作得很好，但它是为无循环的级联层次结构而设计的。与许多计算机科学数据结构和算法一样，当引入循环时它就会崩溃。最好的解决方案是分析模式并查看循环是否确实不可避免。除此之外，插入 NULL 并更新插入后是最好的解决方案。
不幸的是，禁用约束并重新启用是一个很大的禁忌：重新启用必须检查表中的每一行以验证约束，并且将永远持续。否则，约束在数据库元数据中被标记为“不受信任”，优化器基本上会忽略它（仍然会强制执行，但您不会从中获得计划优化的好处）。
Imagine how would you implement that.
If foreign key resultion would be deffered until transaction commit, the commit would have to do all the lookups/checks/cascade operation that did not occur at insert/delete/update time. Think what a FK constraint really means: your insert execution plan gets 'annotated' with additional operation to validate and enforce the FK constraint. If you defer the constraint, the additional logic in the query plan would have to be unbound from the execution moment and pushed into some transaction context so that it is executed at commit time. All of the sudden commit turns from the short 'mark transaction commited in log' operation into an operation that does every thing that was skipped during the actual transaction. The worst part is that the constraint may fail, and think how would an application handle the failure? With a constraint enforced at the moment the insert is executed, the application can catch the error and take corrcetive actions: it kows exactly what failed. But if you defer this until commit, you try to commit and catch an exception, now you need to somehow figure out, from the cought exception, what failed. Think how complicated the life of application developers would be in that case.
A second reason why this wouldn't work is that you still didn't solve the problem. You have table A with FK constraint into B. You begin transaction, insert into B, then insert into A, then delete from A, then delete from B, then commit. All operations were satisfying the FK at the moment they occured, the database satisfies the FK at commit time. Yet if you defer the constraint checks, they will fail at commit time!!
So I'd say that the referential integrity works fine as it is, but it is designed fot a cascadin hierachy free of cycles. As many CS data structures and algorithms, it breaks when cycles are instroduced. The best solution would be to analyze the schema and see if the cycles are trully unavoidable. Short of that, inserting NULL and updating post insert is the best solution.
Unfortunately disabling the constraint and enabling back is a big no-no: the re-enable would have to check every row in the table to verify the constraint, and will last forever. Otherwise the constraint is marked as 'untrusted' in the database metadata and the optimizer will basically ignore it (will still be enforced, but you don't get plan optimization benefits from it).