SQL Compare 10 does not correctly report CHECK CONSTRAINT

mdesousa

I'm testing a trial of SQL Compare 10.1, and ran into an issue when the "NOCHECK" flag of our constraints is compared... it looks like SQL Compare doesn't accurately reflect the status of the flag.

To reproduce, create a database with some tables and some foreign key constraints.
Take a snapshot of the database.
When I run SQL Compare at this point, no differences are reported.
Then run this command:

alter table <SomeTable> nocheck constraint <SomeConstraint>

Run a comparison... SQL Compare shows the NoCheck flag.
Now run this command to re-enable the check:

alter table <SomeTable> check constraint <SomeConstraint>

At this point, SQL Compare still shows the NOCHECK flag, which is wrong.

Has anyone else run into this problem?

Brian Donahue

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.

mdesousa

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.

Brian Donahue

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

Does it work if you use this syntax?

ALTER TABLE [dbo].[SystemSW] CHECK CHECK CONSTRAINT [FK_SystemSW_Software]

mdesousa

That syntax does not appear to be valid:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CHECK'.

Brian Donahue

Sorry, I forgot the WITH...
ALTER TABLE tablename WITH CHECK CHECK CONSTRAINT constraintName

mdesousa

Excellent! This solves the problem.
Thank you.