Issue when comparing tables with unique constraint

[Bastiaan Molsbeck]

What is de correct link for the v10 documentation, and is the information concerning this issue there different?

[Brian Donahue]

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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[Bastiaan Molsbeck]

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:

[Bastiaan Molsbeck]

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.