SQLServer禁用、啟用外鍵約束

標籤：style   blog   http   os   ar   使用   for   sp   資料   

---啟用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;

 

參考：
 Enable/Disable Constraint in SQLServer 
 sp_MSforeachtable使用方法

SQLServer禁用、啟用外鍵約束