不能截断表，因为它正在被FOREIGN KEY约束引用。 [英] Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

问题描述

使用MSSQL2005，如果我首先截断子表（具有FK关系主键的表），是否可以截断有外键约束的表？

我知道我可以不使用where子句使用 DELETE ，并且

• 然后 RESEED 标识（或）
  
• 删除FK，截断表并重新创建FK。 $ b

我认为只要我在父表格之前截断了子表格，我没有做任何一个选项，但是我得到这个错误：
$ b

无法截断表'TableName'，因为它被FOREIGN KEY约束引用。

解决方案

正确;你不能截断一个有FK约束的表。

通常我的过程是这样的：

1. 删除约束


2. 截断表


3. 重新创建约束。 >
   
   

   （当然，所有的交易都是这样。）
   
   

   当然，这只适用于子有已经被截断。否则，我走一个不同的路线，完全依赖于我的数据看起来像。 （太多的变量进入这里）。
   
   原始的海报确定了为什么是这样;请参阅此答案了解更多详情。

   
   

   Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)?

   I know that I can either

   • Use a DELETE without a where clause and then RESEED the identity (or)
   • Remove the FK, truncate the table, and recreate the FK.

   I thought that as long as I truncated the child table before the parent, I'd be okay without doing either of the options above, but I'm getting this error:

   Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.

   解决方案

   Correct; you cannot truncate a table which has an FK constraint on it.

   Typically my process for this is:

   1. Drop the constraints
   2. Trunc the table
   3. Recreate the constraints.

   (All in a transaction, of course.)

   Of course, this only applies if the child has already been truncated. Otherwise I go a different route, dependent entirely on what my data looks like. (Too many variables to get into here.)

   The original poster determined WHY this is the case; see this answer for more details.

   这篇关于不能截断表，因为它正在被FOREIGN KEY约束引用。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！