SQLSERVER清空(Truncate)被外键引用的数据表

前言：我们知道SQLSERVER清空数据表有两种方式Delete和Truncate，当然两者的不同大家也都知道(不清楚的可以MSDN)。不过这个错误“Cannot truncate table  because it is being referenced by a FOREIGN KEY” 相信大家也都遇到过，解决的已解决，未解决的且看下文。

如何解决

开始我以为只要将外键Disable掉就可以了，事实证明是没用的。其实MSDN已经明确告诉了我们：

不能对以下表使用 TRUNCATE TABLE：

• 由 FOREIGN KEY 约束引用的表。（您可以截断具有引用自身的外键的表。）
• 参与索引视图的表。
• 通过使用事务复制或合并复制发布的表。

对于具有以上一个或多个特征的表，请使用 DELETE 语句。

TRUNCATE TABLE 不能激活触发器，因为该操作不记录各个行删除

难道我真的要用Delete吗？可我真的不想用Delete。原因就在于Truncate的优点，MSDN说：

与 DELETE 语句相比，TRUNCATE TABLE 具有以下优点：

• 所用的事务日志空间较少。
  DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一个项。TRUNCATE TABLE 通过释放用于存储表数据的数据页来删除数据，并且在事务日志中只记录页释放。
• 使用的锁通常较少。
  当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。TRUNCATE TABLE 始终锁定表和页，而不是锁定各行。
• 如无例外，在表中不会留有任何页。
  执行 DELETE 语句后，表仍会包含空页。(略去例如)

好了，下面就来说一下解决方法。

解决方案

1.使用Delete

a) 先Delete依赖表（或叫从表）

b) 再Delete被依赖表（或叫主表）

2.使用Truncate

a) 先备份依赖表外键

b) 删除依赖表外键

c) Truncate主表

d) 重新创建依赖表外键

一段脚本

其实是一个使用Truncate进行处理的存储过程，思路见上。

  1 USE <YOUR DB>
  2 GO
  3 
  4 CREATE PROCEDURE [dbo].[usp_Truncate_Table]
  5   @TableToTruncate VARCHAR(64)
  6 AS 
  7 
  8 BEGIN
  9 
 10 SET NOCOUNT ON
 11 
 12 --==变量定义
 13 DECLARE @i int
 14 DECLARE @Debug bit
 15 DECLARE @Recycle bit
 16 DECLARE @Verbose bit
 17 DECLARE @TableName varchar(80)
 18 DECLARE @ColumnName varchar(80)
 19 DECLARE @ReferencedTableName varchar(80)
 20 DECLARE @ReferencedColumnName varchar(80)
 21 DECLARE @ConstraintName varchar(250)
 22 
 23 DECLARE @CreateStatement varchar(max)
 24 DECLARE @DropStatement varchar(max)   
 25 DECLARE @TruncateStatement varchar(max)
 26 DECLARE @CreateStatementTemp varchar(max)
 27 DECLARE @DropStatementTemp varchar(max)
 28 DECLARE @TruncateStatementTemp varchar(max)
 29 DECLARE @Statement varchar(max)
 30 
 31  SET @Debug = 0--(0:将执行相关语句|1:不执行语句)
 32  SET @Recycle = 0--(0:不创建/不清除存储表|1:将创建/清理存储表)
 33  set @Verbose = 1--(1:每步执行均打印消息|0:不打印消息)
 34 
 35  SET @i = 1
 36     SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>]  WITH NOCHECK ADD  CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
 37     SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
 38     SET @TruncateStatement = 'TRUNCATE TABLE [<ta