SQL Compare 10 does not correctly report CHECK CONSTRAINT

• 
  In my test, the original definition does not change - I get an additional line after I disable the constraint.
  before:

  -- Foreign Keys
  
  ALTER TABLE [dbo].[SystemSW] WITH NOCHECK ADD CONSTRAINT [FK_SystemSW_Software] FOREIGN KEY ([SW]) REFERENCES [dbo].[Software] ([RecID]) ON DELETE CASCADE
  GO
  ALTER TABLE [dbo].[SystemSW] WITH NOCHECK ADD CONSTRAINT [FK_SystemSW_Computers] FOREIGN KEY ([System]) REFERENCES [dbo].[Computers] ([AssetID]) ON DELETE CASCADE
  GO
  

  After:

  -- Foreign Keys
  
  ALTER TABLE [dbo].[SystemSW] WITH NOCHECK ADD CONSTRAINT [FK_SystemSW_Software] FOREIGN KEY ([SW]) REFERENCES [dbo].[Software] ([RecID]) ON DELETE CASCADE
  GO
  ALTER TABLE [dbo].[SystemSW] WITH NOCHECK ADD CONSTRAINT [FK_SystemSW_Computers] FOREIGN KEY ([System]) REFERENCES [dbo].[Computers] ([AssetID]) ON DELETE CASCADE
  GO
  ALTER TABLE [dbo].[SystemSW] NOCHECK CONSTRAINT [FK_SystemSW_Software]
  GO
  

  
  So I think the result is not as you expect because the original constrint definition remains (WITH NOCHECK in this context means the existing data will not be checked) and the additional NOCHECK CONSTRAINT is added to disable the constraint. These two bits of syntax, although they look very similar, fulfill two different functions.

  Brian Donahue March 20, 2012 11:52

  - 0 +
• 
  Hi Brian, thank you for your reply.
  In my example, I was referring exclusively to the second scenario. So, assuming that my table already has all its constraints and they were enabled when they were initially created. I am taking a snapshot of the database and then running this command:
  
  ALTER TABLE [dbo].[SystemSW] NOCHECK CONSTRAINT [FK_SystemSW_Software]
  
  If I run SQL Compare at this point, there is a difference between the snapshot and the live database, which is expected.
  
  Next, I run this command:
  
  ALTER TABLE [dbo].[SystemSW] CHECK CONSTRAINT [FK_SystemSW_Software]
  
  I would expect SQL Compare to show no differences... but it actually still shows that the NOCHECK flag is enabled.

  mdesousa March 20, 2012 13:43

  - 0 +
• 
  Hi,
  
  I see. It looks to be broken in 10.1. It worked in 10.0. I'll see what I can find out.

  Brian Donahue March 21, 2012 05:05

  - 0 +
• 
  Does it work if you use this syntax?
  
  ALTER TABLE [dbo].[SystemSW] CHECK CHECK CONSTRAINT [FK_SystemSW_Software]

  Brian Donahue March 21, 2012 11:37

  - 0 +
• 
  That syntax does not appear to be valid:
  
  Msg 156, Level 15, State 1, Line 1
  Incorrect syntax near the keyword 'CHECK'.

  mdesousa March 27, 2012 13:25

  - 0 +
• 
  Sorry, I forgot the WITH...
  ALTER TABLE tablename WITH CHECK CHECK CONSTRAINT constraintName

  Brian Donahue March 28, 2012 05:10

  - 0 +
• 
  Excellent! This solves the problem.
  Thank you.

  mdesousa March 28, 2012 06:51

  - 0 +

Add comment

Please sign in to leave a comment.