SQLServer禁用、启用外键约束

2022-11-20 18:59:18

---启用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使用方法

--启用or禁用指定表所有外键约束