tsql - ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_SomeCol]

Question

If I script a table with a foreign key, it looks like this:

GO
ALTER TABLE [dbo].[MyTable]  WITH CHECK ADD  CONSTRAINT [FK_MyTable_SomeCol] FOREIGN KEY([SomeCol])
REFERENCES [dbo].[MyOtherTable] ([SomeCol])
GO
ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_SomeCol]
GO

What is the second part for (ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_SomeCol])?

Answer 1

It's an artifact of the way that the constraint is scripted - although it's unnecessary to specify these options (since they're the defaults for new constraints), the same generator can also generate NOCHECK options in exactly the same manner.

Documentation for ALTER TABLE indicates two distinct uses of CHECK/NOCHECK:

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.

And:

{ CHECK | NOCHECK } CONSTRAINT

Specifies that constraint_name is enabled or disabled.

So one option is saying "check the current contents of the table", the other is saying "Validate new data as it is added".