ALTER TABLE CHECK CONSTRAINT

Question

From the Object Explorer in SQL Server, when selecting and scripting a foreign-key constraint, the following code is generated.

     USE [MyTestDatabase]
     GO

     ALTER TABLE [dbo].[T2] WITH NOCHECK ADD CONSTRAINT [FK_T2_T1] FOREIGN KEY([T1ID])
     REFERENCES [dbo].[T1] ([T1ID])
     GO

     ALTER TABLE [dbo].[T2] CHECK CONSTRAINT [FK_T2_T1]
     GO

What is the purpose of the last statement "ALTER TABLE CHECK CONSTRAINT"? It doesn't seem to matter whether or not it is run. It does not fail on existing bad data, nor does it change that the constraint will be enforced on new data.

Thanks!

Answer 1

It ensures that the constraint is enabled after it is created. Your ALTER TABLE statement includes WITH NOCHECK which is the piece that says not to check for existing bad data during the creation of the constraint.

As written, the existing data will not be checked against the constraint because of the WITH NOCHECK in the first statement. Issuing the second statement will enable the check against the constraint for any future changes to the table that are covered by the constraint, up to the point that an ALTER TABLE [dbo].[T2] NOCHECK CONSTRAINT [FK_T2_T1] is issued.

The statements, as written, are basically saying "Create this foreign key constraint but don't check it against existing data. Make it active for any upcoming changes to the data."

Answer 2

Your first statement creates a disabled constraint. It needs to be enabled and possibly trusted. The following strange syntax will make sure your constraint is enabled and trusted:

ALTER TABLE YourTable
      WITH CHECK CHECK CONSTRAINT YourConstraint;

There is a very good blog post by Hugo Kornelis that explains it in good detail: Can you trust your constraints