CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
Jeff Stock
Jeff Stock

Reputation: 3856

Enable foreign key with Check existing data

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.

Upvotes: 11

Views: 20002

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300559

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

Upvotes: 16

Related Questions