SQL Server：如何忽略参考完整性，直到COMMIT？ [英] SQL Server: How to ignore referential integrity until COMMIT?

问题描述

我有一个从一个数据库移动到另一个数据库的过程。由于一些循环的外键引用链，我不能删除旧数据库中的行，也不能将它们插入到新数据库中。

由于整个操作发生在事务 ¹ ，我希望SQL Server忽略引用完整性失败，直到我调用 COMMIT TRANSACTION 。

例如 ² ：

 表：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 transaction¹, i want SQL Server to ignore referential integrity failures until i call COMMIT TRANSACTION.

For example²:

   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.³

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

¹distributed
²contrived hypothetical
³which 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屋！