How can we help you today? How can we help you today?

Check Constraint logic changed on synchronize

I've come across an issue with CHECK constraints where the logic can actually be changed when synchronising from a scripts folder to a database. It seems that SQL Compare is parsing the check constraint at compare time. For example, it will convert an "col1 IN (1,3) to "col1 = (3) OR col1 = (1)". This in itself is fine, but the problem is when using brackets within a more complex CHECK constraint. SQL Compare does not correctly keep the brackets which can lead to a change in the logic. The example below illustrates this.

Table definition file dbo.myTable.sql:
CREATE TABLE [dbo].[myTable]
(
col1 int null,
col2 int not null
)
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER TABLE [dbo].[myTable] ADD CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED (col1) ON [PRIMARY]
GO
ALTER TABLE [dbo].[myTable] WITH NOCHECK ADD CONSTRAINT [CK_myTable] CHECK ((NOT col1 IS NULL AND col2 in (1,3)) OR (col1 IS NULL AND col2 = 2))
GO

When running the compare in SQL Compare, the check constraint becomes:
ALTER TABLE [dbo].[myTable] WITH NOCHECK ADD CONSTRAINT [CK_myTable] CHECK ((NOT [col1] IS NULL AND [col2]=(3) OR [col2]=(1) OR [col1] IS NULL AND [col2]=(2)))

i.e. the brackets have been removed, and the logic is not the same (note the missing bracket after the "[col2]=(1)".

I have seen this issue with SQL Compare 7 and 8. Is this a known issue, and what is the best workaround?

Thanks
winstar
0

Comments

3 comments

Sort by
  • peter.peart
    Hi there,

    Can you please confirm the exact version of SQL Compare you are using? There has been a number of fixes made around constraints and script folders in the patch and I wanted to confirm if you are working with the patch already or not.

    If not, you can obtain the latest patch from the below link:

    http://www.red-gate.com/messageboard/vi ... hp?t=11077

    Thanks!

    Pete
    peter.peart
    0
  • winstar
    Hi Pete,

    I was not working with this patch. The version I was using was 8.2.0.16.

    I have just upgraded to the patch, and the result is still the same.

    Thanks,
    Rich
    winstar
    0
  • peter.peart
    Hi there,

    Thanks for your post and sorry that the patch isn't working for you. Is there any chance you would be able to send us the schema creation script, a backup of the DB or a SQL Compare snapshot along with the script folder you are comparing against and the SQL Compare project file? We will then have a go at replicating this in house for you.

    You can send this to support(at)red-gate.com using the subject F0038643 as we have a call open already for you.

    Many thanks!
    peter.peart
    0

Add comment

Please sign in to leave a comment.