SQLServer禁用、启用外键约束

---启用or禁用指定表所有外键约束  
alter table PUB_STRU  NOCHECK constraint all;  
alter table PUB_STRU  CHECK constraint all;  
  
---生成启用or禁用指定表外键约束的sql  
select 'ALTER TABLE ' + b.name + ' NOCHECK CONSTRAINT ' + a.name +';'  from sysobjects a ,sysobjects b where a.xtype ='f' and a.parent_obj = b.id and b.name='表名';  
select 'ALTER TABLE ' + b.name + ' CHECK CONSTRAINT ' + a.name +';'  from sysobjects a ,sysobjects b where a.xtype ='f' and a.parent_obj = b.id and b.name='表名';  

--生成的sql如下
ALTER TABLE PUB_STRU NOCHECK CONSTRAINT PUBSTRU_FK1; 
ALTER TABLE PUB_STRU NOCHECK CONSTRAINT PUBSTRU_FK2; 
ALTER TABLE PUB_STRU CHECK CONSTRAINT PUBSTRU_FK1; 
ALTER TABLE PUB_STRU CHECK CONSTRAINT PUBSTRU_FK2;   

 --查看约束状态（查询字典表 sys.foreign_keys，该字典表开始出现于sqlserver2005及以上版本）：
select name , is_disabled from sys.foreign_keys order by name;  
 --其中：name  ： 外键约束名称   is_disabled : 是否已禁用


  --删除外键
alter table AdItem drop constraint AdOrder_AdItem_FK1

--增加外键
alter table AdItem 
add constraint AdOrder_AdItem_FK1 foreign key (AI_nOrderNo) references AdOrder(AO_nOrderNo)

--单个表的一个外键
alter table Student nocheck constraint FK__Student__SchoolN__4222D4EF  
alter table Student check constraint FK__Student__SchoolN__4222D4EF  

--单个表的所有外键
alter table Student nocheck constraint all  
alter table Student check constraint all  

--某个数据库的所有表
EXEC sp_MSforeachtable @command1='alter table ?  NOCHECK constraint all;
EXEC sp_MSforeachtable @command1='alter table ?  CHECK constraint all;

SQL Server 临时禁用和启用所有外键约束

--获得禁用所有外键约束的语句
select  'ALTER TABLE ['  + b.name +  '] NOCHECK CONSTRAINT ' +  a.name +';'  
from  sysobjects  a ,sysobjects  b    
where  a.xtype ='f' and  a.parent_obj = b.id


--获得启用所有外键约束的语句
select  'ALTER TABLE [' + b.name +  '] CHECK CONSTRAINT ' +  a.name +';'  
from  sysobjects  a ,sysobjects  b    
where  a.xtype ='f' and  a.parent_obj = b.id