Sql-server – ALTER TABLE CHECK CONSTRAINT

check-constraintssql-server-2008

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!

Best Answer

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."

Related Question