Enable foreign key with Check existing data

Question

I love foreign keys, but I'm running into one problem with them. I have a conversion program where I am disabling foreign keys to tables. The reason I'm doing this is so that I can reconvert all records in the main table, but leave the other tables dependent on that one untouched without having to reconvert them every time because they are HUGE.

I'm using these commands to disable and re-enable the foreign keys:

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint

However, after I re-enable the constraint "Check Existing Data on Creation or Re-Enabling" is still set to No. I understand that it is set to No because I disabled the constraint, but by doing this it altered my database schema, which I don't like. I thought this would be considered re-enabling the constraint and would check the existing data, but apparently not.

Is there no way to change this with the ALTER TABLE command? I know I can if I drop the constraint and recreate it, but I'm not about to write the script to recreate every foreign key I have and maintain that.

I'm using SQL Server 2008 R2.

Solution

To re-enable a constraint:

 -- Enable the constraint
 ALTER TABLE MyTable 
 WITH CHECK CHECK CONSTRAINT MyConstraint
 GO

Note: you have to specify CHECK twice to force a check that all foreign key values are valid.

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.

Ref.: Guidelines for Disabling Indexes and Constraints

As noted in comments (for search engines), this corresponds to

sys.foreign_keys.is_not_trusted

in the catalog view

Comments

[leqid]

For the record, because I had a heck of a time finding it elsewhere: "sys.foreign_keys.is_not_trusted" corresponds to "Check Existing Data on Creation or Re-Enabling" in SQL Server Management Studio "Foreign Key Relationships" dialog box.