I am looking at running the following statement which will create a FK constraint.
ALTER TABLE Table2 WITH CHECK ADD
CONSTRAINT FK_Table2_Table1 FOREIGN KEY (SID) REFERENCES Table1 (SID);
(1) My question is: what does the WITH CHECK stand for here?
I cannot easily find the answer on the official MSDN site.
(2) Can you point me to some official documentation too
(on the WITH CHECK and other options that
are available when adding a FK constraint) ?
you can find the answer here: http://technet.microsoft.com/en-us/library/ms190273(v=sql.105).aspx
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 WITH CHECK CHECK CONSTRAINT ALL.
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.
Reference link: http://msdn.microsoft.com/en-us/library/ms190273.aspx
