Red Gate forums :: View topic - Issue when comparing tables with unique constraint
Return to www.red-gate.com RSS Feed Available

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

Issue when comparing tables with unique constraint

Search in SQL Comparison SDK 10 forum
Post new topic   Reply to topic
Jump to:  
Go to page Previous  1, 2
Author Message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

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

What is de correct link for the v10 documentation, and is the information concerning this issue there different?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6667

PostPosted: Wed Nov 28, 2012 11:31 am    Post subject: Reply with quote

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



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Thu Nov 29, 2012 9:45 am    Post subject: Reply with quote

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



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Fri Nov 30, 2012 11:57 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page Previous  1, 2
Page 2 of 2

 
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