Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers 2

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

share|improve this answer
    
Actually I checked that, it doesn't make a difference if there is bad data, the first or second line will not fail. To make with one fail, they'd have to look like this: –  Delux Sep 13 '12 at 20:17
1  
ALTER TABLE [dbo].[T2] WITH CHECK CHECK CONSTRAINT [FK_T2_T1] –  Delux Sep 13 '12 at 20:18
    
Right. But you'll see it fail after that when at some point you try to run an INSERT or an UPDATE that violates the constraint. Neither will fail if bad data exists at the time those 2 statements are executed. –  squillman Sep 13 '12 at 20:19
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.