SQL Compare 10 does not correctly report CHECK CONSTRAINT

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

SQL Compare 10 does not correctly report CHECK CONSTRAINT

Postby mdesousa » Sun Mar 18, 2012 1:38 pm

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?
mdesousa
 
Posts: 12
Joined: Sun Mar 18, 2012 1:36 pm

Postby Brian Donahue » Tue Mar 20, 2012 4:52 pm

In my test, the original definition does not change - I get an additional line after I disable the constraint.
before:
Code: Select all
-- 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:
Code: Select all
-- 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
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby mdesousa » Tue Mar 20, 2012 6:43 pm

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
 
Posts: 12
Joined: Sun Mar 18, 2012 1:36 pm

Postby Brian Donahue » Wed Mar 21, 2012 10:05 am

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
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Wed Mar 21, 2012 4:37 pm

Does it work if you use this syntax?

ALTER TABLE [dbo].[SystemSW] CHECK CHECK CONSTRAINT [FK_SystemSW_Software]
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby mdesousa » Tue Mar 27, 2012 6:25 pm

That syntax does not appear to be valid:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CHECK'.
mdesousa
 
Posts: 12
Joined: Sun Mar 18, 2012 1:36 pm

Postby Brian Donahue » Wed Mar 28, 2012 10:10 am

Sorry, I forgot the WITH...
ALTER TABLE tablename WITH CHECK CHECK CONSTRAINT constraintName
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby mdesousa » Wed Mar 28, 2012 11:51 am

Excellent! This solves the problem.
Thank you.
mdesousa
 
Posts: 12
Joined: Sun Mar 18, 2012 1:36 pm


Return to SQL Compare 10

Who is online

Users browsing this forum: grinnell and 1 guest