我正在使用这些命令来禁用和重新启用外键：
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
但是,在我重新启用约束“检查创建或重新启用时的现有数据”仍然设置为否.我知道它被设置为否因为我禁用了约束,但是通过这样做它改变了我的数据库模式,我不喜欢.我认为这将被视为重新启用约束并将检查现有数据,但显然不是.
有没有办法用ALTER TABLE命令改变它？我知道如果我删除约束并重新创建它,我就可以了,但我不打算编写脚本来重新创建我拥有的每个外键并保持它.
我正在使用sql Server 2008 R2.
解决方法
-- Enable the constraint ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint GO
注意：您必须指定CHECK两次以强制检查所有外键值是否有效.
FOREIGN KEY and CHECK constraints that are disabled are marked
is_not_trusted.These are viewable in the sys.check_constraints and
sys.foreign_keys catalog views. This means that the constraint is no
longer being verified by the system for all rows of the table. Even
when you re-enable the constraint,it will not reverify the existing
rows against the table unless you specify the WITH CHECK option of
ALTER TABLE. Specifying WITH CHECK marks the constraint as trusted
again.
参考：Guidelines for Disabling Indexes and Constraints
sys.foreign_keys.is_not_trusted
在目录视图中
