转贴马克的关于删除存在外键引用的主表之存储过程！！！

最新推荐文章于 2021-02-18 20:06:10 发布

转载最新推荐文章于 2021-02-18 20:06:10 发布 · 1.3k 阅读

文章标签：

#存储 #table #delete #insert #conflict #join

sqlserver2000 专栏收录该内容

15 篇文章

订阅专栏

本文介绍了一种通过存储过程来高效管理SQL中外键约束的方法。该方法允许开发者轻松地开关外键约束，从而简化了涉及外键的表的数据操作流程。

最近在做假资料时经常需要删除一些表中的内容。但是：

设置外键后，想删除表中的数据无法删除，这时需删除外键后重建，
或找到外键后用 alter table 表名 nocheck 外键名　来暂时屏蔽外键，然后删除。---alter table fktable nocheck
干脆写个存储过程，设置外键的开关。
exec fk_switch '表名',0 屏蔽外键 ----status 状态位（bit）
exec fk_switch '表名',1 重启外键

/* 应用:
exec fk_switch 'tableName',0
delete tableName where fieldName = 'abc' -----delete primary table with fk
-- truncate table tableName
exec fk_switch 'tableName',1-------after delete restart fk
*/
Create proc fk_switch @tableName varchar(20),@status bit ----@tablename主表
As
declare @fk varchar(50),@fktable varchar(20)--- 次表FK
declare @s varchar(1000)---语句串
declare cur cursor for ----cursor defination
select b.name as fkname,c.name as fktablename ----得到FK和FKNAME（次表）
from sysforeignkeys a ----外键信息表
join sysobjects b on a.constid = b.id --------与系统表sysobjects JOIN，得到CONSTNAME（约束名），也是OBJ
join sysobjects c on a.fkeyid = c.id---------多次用sysobjects引用，fkeyid得到次表
join sysobjects d on a.rkeyid = d.id---- 主表同下匹配
where d.name = @tableName---FU指定的主表
open cur
fetch next from cur into @fk,@fktable
while @@fetch_status = 0
begin
if @status = 0
   begin
            set @s = 'alter table '+@fktable+' nocheck constraint '+ @fk ---alter table fktable nocheck constraint chname
            print @s
   end
else
begin
       set @s = 'alter table '+@fktable+' check constraint '+ @fk
       print @s
   end
exec(@s)
fetch next from cur into @fk,@fktable
end
close cur
deallocate cur

--以下为测试：
create table A (id int primary key)
go
create table B(id int,
constraint fk_B_A foreign key (id) references A (id))
go
create table C(id int,
constraint fk_C_A foreign key (id) references A (id))
go
insert A values (1)
insert B values(1)
insert C values (1)

--1:
delete a
/*****
服务器: 消息 547，级别 16，状态 1，行 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'fk_B_A'. The conflict occurred in database 'pubs', table 'B', column 'id'.
The statement has been terminated.
*******/

--2:
begin tran
exec fk_switch 'A',0
delete A
exec fk_switch 'A',1
rollback
/*
alter table B nocheck constraint fk_B_A
alter table C nocheck constraint fk_C_A

（所影响的行数为 1 行）

alter table B check constraint fk_B_A
alter table C check constraint fk_C_A
*/

--3: 清除测试表
drop table A,B,C
go