January 22, 2010 at 10:02 am
I've been reading books online and articles on the web and am a little confused on the NO CHECK option for Foreign Key constraints. Please correct me..
If you have a Foreign Key that has the NOCHECK option does that disable the future checking of INSERT UPDATE DELETE operations or will it only ignore the current data in the table? I'm looking at our schema and want to know the significance of a few foreign keys that have the NO CHECK option set.
Thanks for helping to clarify my reading.
January 22, 2010 at 11:50 am
Hi there,
From the BOL it seams that the with NOCHECK allows you to enable a constraint, without checking the already existing values. If there's some values that violates the constraint you can begin to have errors in update staments.
WITH CHECK | WITH NOCHECK
Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.
If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. We do not recommend doing this, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.
The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table CHECK CONSTRAINT ALL.
José Cruz
José Cruz
January 22, 2010 at 3:55 pm
Ok, I now see my confusion by looking closer. I was confusing WITH NOCHECK and NOCHECK CONSTRAINT while reading the syntax of ALTER TABLE. Now it's clear, there are two commands just close in syntax. Thanks!
March 22, 2012 at 7:20 am
I was also confused by using and not using WITH with NOCHECK option.
WITH NOCHECK: does not disable FK constraint, but gets disable for existing records.
NOCHECK without WITH: disable the FK constraint.
Check this: http://sqlwithmanoj.wordpress.com/2012/03/14/nocheck-vs-with-nocheck-while-creating-disabled-foreign-key-fk-constraint/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply