| Author |
Message |
mdesousa
Joined: 18 Mar 2012
Posts: 12
|
 Posted: Sun Mar 18, 2012 1:38 pm Post subject: SQL Compare 10 does not correctly report CHECK CONSTRAINT |
 |
|
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 |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6369
Location: Red Gate Software
|
| Posted: Tue Mar 20, 2012 4:52 pm Post subject: |
|
|
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.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
mdesousa
Joined: 18 Mar 2012
Posts: 12
|
| Posted: Tue Mar 20, 2012 6:43 pm Post subject: |
|
|
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 |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6369
Location: Red Gate Software
|
| Posted: Wed Mar 21, 2012 10:05 am Post subject: |
|
|
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6369
Location: Red Gate Software
|
| Posted: Wed Mar 21, 2012 4:37 pm Post subject: |
|
|
Does it work if you use this syntax?
ALTER TABLE [dbo].[SystemSW] CHECK CHECK CONSTRAINT [FK_SystemSW_Software]
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
mdesousa
Joined: 18 Mar 2012
Posts: 12
|
| Posted: Tue Mar 27, 2012 6:25 pm Post subject: |
|
|
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 |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6369
Location: Red Gate Software
|
| Posted: Wed Mar 28, 2012 10:10 am Post subject: |
|
|
Sorry, I forgot the WITH...
ALTER TABLE tablename WITH CHECK CHECK CONSTRAINT constraintName
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
mdesousa
Joined: 18 Mar 2012
Posts: 12
|
| Posted: Wed Mar 28, 2012 11:51 am Post subject: |
|
|
Excellent! This solves the problem.
Thank you. |
|
| Back to top |
|
|
|
