Red Gate forums :: View topic - SQL Compare 10 does not correctly report CHECK CONSTRAINT
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

SQL Compare 10 does not correctly report CHECK CONSTRAINT

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
mdesousa



Joined: 18 Mar 2012
Posts: 12

PostPosted: Sun Mar 18, 2012 1:38 pm    Post subject: SQL Compare 10 does not correctly report CHECK CONSTRAINT Reply with quote

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?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6670

PostPosted: Tue Mar 20, 2012 4:52 pm    Post subject: Reply with quote

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

-- 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:

-- 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.
Back to top
View user's profile Send private message
mdesousa



Joined: 18 Mar 2012
Posts: 12

PostPosted: Tue Mar 20, 2012 6:43 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6670

PostPosted: Wed Mar 21, 2012 10:05 am    Post subject: Reply with quote

Hi,

I see. It looks to be broken in 10.1. It worked in 10.0. I'll see what I can find out.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6670

PostPosted: Wed Mar 21, 2012 4:37 pm    Post subject: Reply with quote

Does it work if you use this syntax?

ALTER TABLE [dbo].[SystemSW] CHECK CHECK CONSTRAINT [FK_SystemSW_Software]
Back to top
View user's profile Send private message
mdesousa



Joined: 18 Mar 2012
Posts: 12

PostPosted: Tue Mar 27, 2012 6:25 pm    Post subject: Reply with quote

That syntax does not appear to be valid:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CHECK'.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6670

PostPosted: Wed Mar 28, 2012 10:10 am    Post subject: Reply with quote

Sorry, I forgot the WITH...
ALTER TABLE tablename WITH CHECK CHECK CONSTRAINT constraintName
Back to top
View user's profile Send private message
mdesousa



Joined: 18 Mar 2012
Posts: 12

PostPosted: Wed Mar 28, 2012 11:51 am    Post subject: Reply with quote

Excellent! This solves the problem.
Thank you.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group