SQL Server:如何忽略参考完整性,直到COMMIT? [英] SQL Server: How to ignore referential integrity until COMMIT?
问题描述
由于整个操作发生在事务 1 ,我希望SQL Server忽略引用完整性失败,直到我调用 COMMIT TRANSACTION
。
例如 2 :
表:Turboencabulators Table:Marselvanes
== ======================= =======================
PK TurboencabulatorID int / - > PK MarselvaneID int
^ MarselvanesID int - / HasGrammeter bit
| PantametricFan varchar(50)
+ ------------------------------- TurboencabulatorID int
$如果我尝试在新表格中插入 turboencabulator ,那么如果没有 marselvane 已经在那里了。反转的顺序也有同样的问题。
当试图删除旧的行时,我不能删除一个,直到删除另一个。
我试过做一个 n 阶段的系统,其中所有行插入的外键约束设置为<强>空即可。然后我更新所有插入的行,放置正确的缺失值。然后,为了删除源行,我将所有受到 FK 影响的列归零,然后删除实际行。 3
我真正想要的只是做我的T-SQL操作,并且让SQL Server不会告诉我,直到我尝试调用commit。
备注
1 分布式
2 假设的假设
$ b $ pre $ ALTER TABLE whatever_table NOCHECK CONSTRAINT ALL
在开始之前删除约束检查
完成后重新打开...
ALTER TABLE whatever_table CHECK CONSTRAINT ALL
不管怎么样,我都会这么做。
-don
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
you can use ...
ALTER TABLE whatever_table NOCHECK CONSTRAINT ALL
to remove the constraint checking before you begin
and when done turn it back on with ...
ALTER TABLE whatever_table CHECK CONSTRAINT ALL
that's what i'd do anyway.
-don
这篇关于SQL Server:如何忽略参考完整性,直到COMMIT?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!