截断具有外键约束的表 [英] Truncate table with foreign key constraint

问题描述

有什么方法可以在不禁用约束的情况下截断具有外键约束的表?

Is there any way to truncate a table with foreign key constraint without disabling the constraint

推荐答案

简单的答案是:否，您不能.约束的全部要点是约束您做正确的事情(在删除行之前处理即将成为孤立的数据).

The simple answer is no, you cannot. The whole point of constraints is that it constrains you to do the right thing (handle the soon-to-be-orphaned data before deleting the rows).

1. 如果您的问题确实是:
   
   
   有没有办法在不禁用约束的情况下截断具有外键约束的表"
   那么我的答案将是:
   如果引用了另一个表Table2的表Table1的表被截断了，为什么会出现问题.截断Table2可能是一个问题，因为要从Table2中删除的内容很可能仍从Table1中引用.
   因此，按您现在的问题进行，我说继续并截断您想要的所有内容，除非存在涉及到Table1的外键约束的第三个表Table3.
   
   如果构成对表2的外键引用的字段不具有与表2中的记录相关联的值，那么在我上面提到的方案中，外键约束将使您无法将记录插入到表1中.
   
   
   
2. 如果您的问题是这样的:
   
   
   有没有办法在不禁用约束的情况下截断由外键约束引用的表 "，那么答案将是:要看.如果要截断的数据已经从另一个表中引用，并且具有前面的键约束，则将是不可能的，因为会破坏数据完整性.受外键约束引用的数据将受到保护，直到解除约束或删除依赖数据为止.
   
   另一方面，如果要删除的数据尚未被具有该表的外键约束的任何其他表中的任何条目引用，则不会造成问题.
   

1. If your question really is:
   
   
   "Is there any way to truncate a table with foreign key constraint without disabling the constraint"
   Then my answer would be this:
   Why should it be a problem if a table Table1 that references content of another table called Table2 is truncated. It might be a problem to truncate Table2 as the stuff to be deleted from Table2 might well be still referenced from Table1.
   So going by your question as it stands right now I say go ahead and truncate all you want, that is unless there was some third table Table3 involved that would have a foreign key constraint towards Table1.
   
   A foreign key constraint in the scenario I mentioned above will keep you from inserting records into Table1, if the fields that make up the foreign key reference towards Table2 do not have values that are associated with a record in Table2.
   
   
   
2. If your question was meant to be this:
   
   
   "Is there any way to truncate a table that is referenced by a foreign key constraint without disabling the constraint"
   Then the answer would be: It depends. If the data to be truncated was already referenced from another table with a foreing key constraint, it would be impossible as the data integrity would be violated. Data that is referenced by a foreign key constraint is protected until either the constraint is lifted or the dependent data has been deleted.
   
   On the other hand if the data to be deleted was not yet referenced by any entries in any other table with a foreign key constraint towards this table, it would not pose a problem.
   

问候，

Manfred

Regards,

Manfred

这会在数据库中造成不一致，并且不允许这样做，您必须禁用或删除约束，然后截断...

That creates inconsistency in DB, and it is not allowed, either you have to disable or drop constraint then truncate...

这篇关于截断具有外键约束的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！