Difference between WITH NOCHECK CHECK CONSTRAINT and CHECK CONSTRAINT

Question

What is the difference between these scripts?

ALTER TABLE Post_Send WITH NOCHECK CHECK CONSTRAINT [My_FOREIGN_KEY];  
GO

ALTER TABLE Post_Send CHECK CONSTRAINT [My_FOREIGN_KEY];
GO

Solution

CHECK CONSTRAINT enables a constraint. (Yes, it should have been ENABLE instead.) WITH NOCHECK does so without checking existing data. So the confusing syntax WITH NOCHECK CHECK CONSTRAINT enables a constraint without checking existing data.

From the manual:

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.

Since you're re-enabling an existing constraint, WITH NOCHECK is the default, and these two statements do exactly the same thing. WITH CHECK CHECK CONSTRAINT would re-enable the constraint while also checking existing data for violations (and marking the foreign key as trusted for optimizations in the process, which doesn't happen if you bypass the check).

Comments

[Selçuk Kılınç]

They're showing the same effect.

[Shahryar Saljoughi]

links may broke, consider briefing what the links suggest, or give an answer based on the links.