Enable foreign key with validation of existing data

Question

I like foreign keys, but I have one problem with them. I have a conversion program where I turn off foreign keys for tables. The reason I do this is because I can convert all the records to the main table, but leave the rest of the tables dependent on this untouched, without relearning them every time, because they are HUGE.

I use these commands to disable and re-enable foreign keys:

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint

However, after you re-enable the "Check Existing Data for Creation or Re-Enable" constraint, the value is still set to No. I understand that it is set to "No" because I disabled the restriction, but by doing this, he changed my database schema, which I do not like. I thought this would be seen as re-enabling the constraint and would check for existing data, but apparently not.

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

I am using SQL Server 2008 R2.

Answer 1

To enable the restriction again:

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

Note : you need to specify CHECK twice to verify that all foreign key values ​​are valid.

Disabled restrictions for the ID KEY and CHECKS, which are disabled is_not_trusted.These can be viewed in sys.check_constraints and the Directory View sys.foreign_keys. This means that the constraint is not longer checked by the system for all rows of the table. Even when you turn on the constraint again, it will not repeat rows across the table unless you specify the WITH CHECK ALTER TABLE option. Specifying WITH CHECK means the constraint is reliable again.

Ref .: Recommendations for disabling indexes and restrictions

As noted in the comments (for search engines), this is consistent

sys.foreign_keys.is_not_trusted

in catalog view