SQL Server Truncate表遇到外键约束

今天使用动态语句批量删除表数据时，遇到个讨厌的东西——外键约束；

创建表Test1和Test2

Create table test1
(
ID int primary key identity(1,1)
,title varchar(100)
);
Create table test2
(
ID int,
title varchar(100),
constraint FK_TEST2_ID foreign key (ID) references test1(ID)
);

删除数据两种方式分析：

一、Delete （a.自增键接着上次的自增值继续自增;b.可以事务回滚)

方法一：先删除子表（test2）再删除父表（子表有数据就删除父表会报错）；

delete from test2;

delete from test1;

缺点：如果是固定的表则好办，如果是不固定的未知的需动态语句执行的，或者批量的表数据量大的，则需要先根据外键信息对表进行排序；

方法二：先删除外键约束再删除数据最后再重新创建外键约束

alter table test2 drop constraint FK_TEST2_ID;

delete from test2;

delete from test1;

alter table test2 add constraint FK_TEST2_ID foreign key (ID) references  test1(ID);

缺点：删除索引无法回滚，如果动态语句封装在SP中执行的话，一旦有报错，则无法回滚，有风险；

方法三：先失效外键约束，再删除数据，再生效外键约束

alter table test2 nocheck  constraint  all;

delete from test2;

delete from test1;

alter table test2 check  constraint  all;

缺点：同方法二，DML操作无法回滚

Delete总缺点：自增值未重置，会继续自增。

二、Truncate（a.自增键从初始值重新开始;b.无法事务回滚）

只能删除外键约束再truncate，最后再重新创建外键约束。

 

由于本次主要目的是：1.删除数据；2.重置自增值；

综合考虑，所以采取方法一+重置自增值（DBCC CHECKIDENT （tablename,reseed,1）; 

存储过程如下：

 

CREATE PROC [dbo].[Isp_oDelete]
--exec Isp_oDelete 0
@RetVal INT OUTPUT
AS
BEGIN
declare @sql nvarchar(500);
declare @name varchar(100);
declare @pk_min int,@pk_max int;
declare @tran varchar(50);
declare @xorder int;
declare @tablename table
(
tablename varchar(100),
FKName varchar(100),
xorder int
)
declare @tablenameIdentity table
(
ID int identity(1,1),
tablename varchar(100),
seed int
)
DECLARE @GL_MaxNo INT,@GL_RowNo INT

/*
考虑外键约束，将对象排序，从表数据先删除，主表数据后删除
*/

--插入待删除的业务表
INSERT INTO @tablename(tablename,FKName,xorder)
SELECT name,',',100
FROM sysobjects
WHERE name LIKE 'o%' AND xtype='u'

-- 插入具有自增列的表
insert into @tablenameIdentity(tablename,seed)
select a.tablename,ident_seed(name)
from @tablename a
inner join sysobjects b on a.tablename=b.name and ident_seed(name) is not null;

SELECT @GL_MaxNo = @@IDENTITY
,@GL_RowNo = 1;

--更新外键
update a
set a.FKname=a.FKname+object_Name(referenced_object_ID)+','
from @tablename a
inner join sys.foreign_keys b on a.tablename=object_Name(parent_object_id);

--更新排序
set @xorder = 100;
while(exists(select 1 from @tablename a,@tablename b where a.tablename<>b.tablename and charindex(','+a.tablename+',',b.FKName)>0 and b.FKname is not null and b.xorder=@xorder))
begin
update a
set a.xorder=a.xorder-1
from @tablename a
where exists(select 1 from @tablename b where a.tablename<>b.tablename and charindex(','+a.tablename+',',b.FKName)>0 and b.FKname is not null and b.xorder=@xorder);

set @xorder =@xorder-1;
end;

begin transaction
--执行删除，按照xorder 从大到小
WHILE(exists (select 1 from @tablename))
BEGIN
set @name=(select top 1 tablename from @tablename order by xorder desc );

SELECT @sql= 'delete from '+tablename+CHAR(13)
FROM @tablename
WHERE tablename=@name;
select @sql;
EXEC sp_ExecuteSQL @sql;

IF @@Error <> 0
GOTO ErrM

delete from @tablename where tablename=@name;
END;

--设置自增列为初始值
WHILE(@GL_RowNo <= @GL_MaxNo)
BEGIN
select @sql='DBCC CHECKIDENT('+tablename+',reseed,'+cast(seed as varchar(10))+')' from @tablenameIdentity;
EXEC sp_ExecuteSQL @sql;
SET @GL_RowNo=@GL_RowNo+1;
end;

IF @@Error <> 0
GOTO ErrM


COMMIT;
SET @RetVal = 0
RETURN @RetVal

ErrM:
ROLLBACK TRANSACTION
SET @Retval = -1
RETURN @Retval

END

使用truncate如下：

CREATE PROC [dbo].[Isp_oDelete]
--exec Isp_oDelete 0
@RetVal INT OUTPUT
AS
/*
--批量删除模块业务数据
*/
BEGIN
declare @sql nvarchar(500);
declare @name varchar(100);
declare @pk_min int,@pk_max int;
declare @tran varchar(50);
declare @tablename table
(
ID int identity(1,1),
tablename varchar(100)
)
declare @PKnametable table
(
ID int identity(1,1),
name varchar(100),
Mname varchar(100),
pkname varchar(100),
Mcolumn varchar(50),
Fcolumn varchar(50)
)
DECLARE @GL_MaxNo INT,@GL_RowNo INT

--插入待删除的业务表
INSERT INTO @tablename(tablename)
SELECT name
FROM sysobjects
WHERE name LIKE 'o%' AND xtype='u'
ORDER BY name

SELECT @GL_MaxNo = @@IDENTITY
,@GL_RowNo = 1

set @tran='A';
BEGIN tran
insert into @pknametable(name,mname,pkname,Mcolumn,Fcolumn)
select object_Name(parent_object_id) as name,object_Name(referenced_object_ID),a.name as pkname,c.name as fcolumn,d.name as Mcolumn
from sys.foreign_keys a
inner join sysforeignkeys b on a.[object_id]=b.constid
inner join syscolumns c on b.fkeyID=c.ID and b.fkey=c.colid
inner join syscolumns d on b.rkeyID=d.ID and b.rkey=d.colid
where exists(select 1 from @tablename where object_Name(referenced_object_ID)=tablename) and isnull(is_disabled,0)=0;
set @pk_min=1;
select @pk_max=max(ID) from @pknametable;
--2.删除外键约束
while @pk_min<=@pk_max
begin
select @sql='alter table '+name+' drop constraint '+ pkname from @pknametable where ID=@pk_min;
select @sql;
EXEC sp_ExecuteSQL @sql;
set @pk_min=@pk_min+1;
end;

--循环删除表数据
WHILE(@GL_RowNo <= @GL_MaxNo)
BEGIN
select @name=tablename from @tablename where ID=@GL_RowNo;

--清空表
SELECT @sql= 'truncate table '+tablename+CHAR(13)
FROM @tablename
WHERE ID=@GL_RowNo;
select @sql;
EXEC sp_ExecuteSQL @sql;

IF @@Error <> 0
GOTO ErrM

SET @GL_RowNo=@GL_RowNo+1;
END

--重新创建外键约束
set @pk_min=1;
while @pk_min<=@pk_max
begin
select @sql='alter table '+name+' add constraint '+ pkname +' foreign key( '+Fcolumn+' ) references '+mname+' ( '+Mcolumn+' ) ' from @pknametable where ID=@pk_min;
EXEC sp_ExecuteSQL @sql;
select @sql;
set @pk_min=@pk_min+1;
end;

IF @@Error <> 0
GOTO ErrM


COMMIT;
SET @RetVal = 0
RETURN @RetVal

ErrM:
ROLLBACK TRANSACTION
SET @Retval = -1
RETURN @Retval

END