Issue when comparing tables with unique constraint

Automate and integrate using the SQL comparison API

Moderators: Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

Postby Bastiaan Molsbeck » Wed Nov 28, 2012 10:33 am

What is de correct link for the v10 documentation, and is the information concerning this issue there different?
Bastiaan Molsbeck
 
Posts: 48
Joined: Fri Mar 26, 2010 11:12 am
Location: The Netherlands

Postby Brian Donahue » Wed Nov 28, 2012 11:31 am

I would like to re-iterate: I cannot reproduce your issue. If you run my code, does it drop the constraint?

I am using Redgate.SQLDataCompare.Engine v 10.0.1.101

I cannot comment about the documnentation, I have once again notified the product management about the problems with the documentation.

I would really like to get this working for you but I don't believe SchemaMappings are the way forward, but as you point out, I can't prove that with the documentation in the current state it is in.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Bastiaan Molsbeck » Thu Nov 29, 2012 9:45 am

Hi, I tried you suggestion, as suggested.

I changed my code so that it is the same as yours:
- I used the "TableMappings" instead of the "SchemaMappings"
- I used the same "EngineDataCompareOptions" as you specfied, which includes the "DropConstraintsAndIndexes", and assigned it to the "TableMappings", "ComparisonSession" and "SqlProvider"
- I do not call the method "ReplayUserActions" anymore
- I added the "For...Next" loop to include all TableMappings after the "CreateMappings" call

But:
The unique constraint is NOT dropped!
The script generated is somewhat bigger (because more tables are included), but regarding the concerning table ("tbdRole") the script is exactly the same:

Code: Select all
-- Drop constraints from [dbo].[tbdRole]
ALTER TABLE [dbo].[tbdRole] DROP CONSTRAINT [FK_tbdRole_tbdTemplate]

-- Drop unused indexes from [dbo].[tbdRole]
DROP INDEX [IX_tbdRole_fldTemplateID] ON [dbo].[tbdRole]

-- Update 2 rows in [dbo].[tbdRole]
UPDATE [dbo].[tbdRole] SET [fldTemplateID]=12 WHERE [fldRoleID]=1001
UPDATE [dbo].[tbdRole] SET [fldTemplateID]=13 WHERE [fldRoleID]=1002

-- Add indexes to [dbo].[tbdRole]
CREATE CLUSTERED INDEX [IX_tbdRole_fldTemplateID] ON [dbo].[tbdRole] ([fldTemplateID]) ON [PRIMARY]

-- Add constraints to [dbo].[tbdRole]
ALTER TABLE [dbo].[tbdRole] WITH NOCHECK ADD CONSTRAINT [FK_tbdRole_tbdTemplate] FOREIGN KEY ([fldTemplateID]) REFERENCES [dbo].[tbdtTemplate] ([fldTemplateID])
ALTER TABLE [dbo].[tbdRole] NOCHECK CONSTRAINT [FK_tbdRole_tbdTemplate]

Could it be that the difference in version of the component "Redgate.SQLDataCompare.Engine" is the reason?
I currently use version 10.0.1.69.
Is it possible that I can obtain your version (build 101), to test this?
Bastiaan Molsbeck
 
Posts: 48
Joined: Fri Mar 26, 2010 11:12 am
Location: The Netherlands

Postby Bastiaan Molsbeck » Fri Nov 30, 2012 11:57 am

I have found what did cause the problem!

Apparently the unique contraint on the concerning table "tbdRole" was DISABLED.
Therefore, your component does not generate the DROP statement for this constraint.

This should be not a problem, because a disabled unique constraint would not fire when a duplicate value is inserted.

But because a CLUSTERED index was also present on the table, this index is dropped.
And dropping a clustered index results in a rebuild of ALL other indexes, including the unique constraint.
This means that after the DROP INDEX statement, the unique constraint was enabled again, and prevents the data from being modified!

The solution for me would be to enable all unique constraints first, because then your component does generate the DROP statements for them.

But I would like to know why your component doesn't generate a DROP statement for disabled unique constraints, because dropping indexes could enabled them.
Bastiaan Molsbeck
 
Posts: 48
Joined: Fri Mar 26, 2010 11:12 am
Location: The Netherlands

Previous

Return to SQL Comparison SDK 10

Who is online

Users browsing this forum: No registered users and 0 guests