[转] SQL Server 批量 停用/启用 外键约束

本文转自：http://hi.baidu.com/wangzhiqing999/item/ca699308de4f1ff9a1103429

今天百度知道上面,看到这样一个要求:

现在有一个库,有很多张表
想要删除一张表的记录的时候,由于外键关联太多,
所以,没法删除相应的记录,
谁能帮忙写个存储过程,就是先删除所有表的主,外键,
然后进行删除表记录,然后再恢复之前所有的主外键.


一眼看上去，需要批量删除所有外键，并不困难。
但是要求批量所有外键之后，
一切处理完毕后，还要把外键重建回来。
这个有点复杂了。

心想，如果删除之后，还要重建的。
那还不如一开始就不删除，只是暂时 “不可用”。
等一系列的操作执行完毕后，
再把这些前面暂时 “不可用” 的外键  “恢复使用”

 

首先生成 停用 外键的SQL语句

 

select 

  'ALTER TABLE '+o.name+' NOCHECK CONSTRAINT '+fk.name+';'  AS  Command

from 

  sys.foreign_keys  fk  

  JOIN  sys.all_objects  o  ON (fk.parent_object_id=o.object_id)

 

具体会有多少条记录，取决于你的数据库里面，有多少个外键了。
在我的测试数据库里面，只有一个外键。
所以我的执行结果为：

ALTER TABLE test_sub NOCHECK CONSTRAINT main_id_cons;

 

把所有的执行结果，都去执行一遍， 就可以将所有的 外键约束停用。

 

下面是执行的测试：

 

1> delete  from test_main
2> go
消息 547，级别 16，状态 1，服务器 GMJ-PCSQLEXPRESS，第 1 行
DELETE 语句与 REFERENCE 约束"main_id_cons"冲突。该冲突发生于数据库"Test"，表"dbo
.test_sub", column 'main_id'。
语句已终止。
1>ALTER TABLE test_sub NOCHECK CONSTRAINT main_id_cons;
2> go
1> delete from test_main
2> go

(2 行受影响)
1> delete from test_sub
2> go

(2 行受影响)

 

数据清理完毕后，恢复外键

select 

  'ALTER TABLE '+o.name+' CHECK CONSTRAINT '+fk.name+';'  AS  Command

from 

  sys.foreign_keys  fk  

  JOIN  sys.all_objects  o  ON (fk.parent_object_id=o.object_id)

 

我的执行结果为：

ALTER TABLE test_sub CHECK CONSTRAINT main_id_cons;

 

测试外键约束是否启用了

 

1> ALTER TABLE test_sub CHECK CONSTRAINT main_id_cons;
2> go
1> INSERT INTO test_sub VALUES (1, 2 , 'A');
2> go
消息 547，级别 16，状态 1，服务器 GMJ-PCSQLEXPRESS，第 1 行
INSERT 语句与 FOREIGN KEY 约束"main_id_cons"冲突。该冲突发生于数据库"Test"，表"d
bo.test_main", column 'id'。
语句已终止。