| Author |
Message |
Bastiaan Molsbeck
Joined: 26 Mar 2010
Posts: 38
Location: The Netherlands
|
 Posted: Fri Nov 23, 2012 9:16 pm Post subject: Issue when comparing tables with unique constraint |
 |
|
Hi,
I created an application using the Red-Gate SQL Comparison SDK 10 to compare two databases (which are identical by schema but differ in data) and synchronize them after the comparison.
Now I run into an issue for tables with a unique constraint. Let me explain it using an example.
The table definition looks something like this:
| Code: |
CREATE TABLE tbdRole
(
fldRoleID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
fldRoleType INT NOT NULL,
fldTemplateID INT NOT NULL,
fldName VARCHAR(255) NOT NULL,
fldReadRole BIT NOT NULL
)
-- make sure all roles for a template have a unique name
ALTER TABLE tbdRole
ADD CONSTRAINT UN_tbdRole UNIQUE NONCLUSTERED
(
fldTemplateID,
fldName
) |
Now, this table in the source database contains (amongst others) these two rows:
1001, 1, 12, "Role_1", 0
1002, 1, 13, "Role_1", 0
This table in the target databas contains (amongst others) these two rows:
1001, 1, 13, "Role_1", 0
1002, 1, 12, "Role_1", 0
When comparing these two tables, the following script is generated:
| Code: |
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)
-- 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].[tbdTemplate] ([fldTemplateID])
ALTER TABLE [dbo].[tbdRole] NOCHECK CONSTRAINT [FK_tbdRole_tbdTemplate]
COMMIT TRANSACTION
GO |
When running this script, the following error occurs when executing the first UPDATE statement, because of the existing unique constraint:
| Code: |
| Violation of UNIQUE KEY constraint 'UN_tbdRole'. Cannot insert duplicate key in object 'dbo.tbdRole'. |
NOTE: I'm aware of the SqlOptions.DropConstraintsAndIndexes, but having set this or not does not matter. The exact same script is generated.
How can I address this issue?
Do I have to manually drop and recreate all unique contraints? |
|
| Back to top |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6345
Location: Red Gate Software
|
| Posted: Mon Nov 26, 2012 4:28 pm Post subject: |
|
|
It doesn't look as if SQL Data Compare Engine is automatically setting UN_tbdRole as the comparison key, or mappings have not been created properly.
AFAIK what you want to happen is a DELETE or INSERT rather than an UPDATE, if the row identifiers are the columns that are part of the UN_tbdRole constraint.
| Code: |
tableMapping.MatchingMappings.Add(new
FieldMapping(tableMapping.Obj1.Fields["fldTemplateID"], tableMapping.Obj2.Fields["fldTemplateID"], FieldMappingStatus.Success));
tableMapping.MatchingMappings.Add(new
FieldMapping(tableMapping.Obj1.Fields["fldName"], tableMapping.Obj2.Fields["fldName"], FieldMappingStatus.Success));
|
_________________
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 |
|
|
Bastiaan Molsbeck
Joined: 26 Mar 2010
Posts: 38
Location: The Netherlands
|
| Posted: Mon Nov 26, 2012 4:33 pm Post subject: |
|
|
Yes Brian, that is correct:
I rather would like a DELETE/INSERT instead of an UPDATE, because of the unique constraint, or a way to ignore the unique constraint.
Do I achieve this with your posted code?
Sidenote: the comparison key is set to the primary key of the table. We have some tables that have more than one unique key constraint, therefore we always set the comparison key on the primary key. |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6345
Location: Red Gate Software
|
| Posted: Mon Nov 26, 2012 6:05 pm Post subject: |
|
|
If you are not using the unique constraint columns as the comparison key, you should be able to get the script to run using the "DropConstraintsAndIndexes" option.
| Code: |
EngineDataCompareOptions opts = new EngineDataCompareOptions(MappingOptions.Default, ComparisonOptions.Default, SqlOptions.Default | SqlOptions.DropConstraintsAndIndexes);
// make sure to apply the "opts" value to ComparisonSession, Mappings, and SqlProvider for consistent results
|
_________________
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 |
|
|
Bastiaan Molsbeck
Joined: 26 Mar 2010
Posts: 38
Location: The Netherlands
|
| Posted: Mon Nov 26, 2012 8:26 pm Post subject: |
|
|
No, I'm sorry.
The script I posted was generated WITH the DropConstraintsAndIndexes turned ON:
| Code: |
Dim objMappings As New SchemaMappings()
objMappings.Options.ComparisonOptions = ComparisonOptions.Default
objMappings.Options.MappingOptions = MappingOptions.Default
objMappings.Options.SqlOptions = SqlOptions.Default
objMappings.Options.SqlOptions = (objMappings.Options.SqlOptions Or SqlOptions.DisableKeys)
objMappings.Options.SqlOptions = (objMappings.Options.SqlOptions Or SqlOptions.DropConstraintsAndIndexes)
objMappings.Options.SqlOptions = (objMappings.Options.SqlOptions Or SqlOptions.DisableTriggers)
...
objMappings.CreateMappings(objSourceDB, objTargetDB)
...
Using objComparisonSession As ComparisonSession = New ComparisonSession()
objComparisonSession.Options = objMappings.Options
objComparisonSession.CompareDatabases(objSourceDB, objTargetDB, objMappings)
...
Using objExecutionBlock As ExecutionBlock = New SqlProvider() { _
.Options = objMappings.Options _
}.GetMigrationSQL(objComparisonSession, True)
New BlockExecutor().ExecuteBlock(objExecutionBlock, Configuration.SQLServer_Target, objDatabase.Target, Configuration.TrustedConnection_Target, Configuration.UserName_Target, Configuration.Password_Target)
End Using
End Using |
I'm using v10.0.1 of the SDK, if you might want to know this.
Any other ideas? Am I missing something in the code? |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6345
Location: Red Gate Software
|
| Posted: Tue Nov 27, 2012 10:43 am Post subject: |
|
|
Don't know. Will escalate this to the development team.
_________________
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 |
|
|
Bastiaan Molsbeck
Joined: 26 Mar 2010
Posts: 38
Location: The Netherlands
|
| Posted: Tue Nov 27, 2012 10:51 am Post subject: |
|
|
Thank you Brian.
How will I receive updates on this issue? Do they also post in this topic? |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6345
Location: Red Gate Software
|
| Posted: Tue Nov 27, 2012 4:03 pm Post subject: |
|
|
Does UN_tbdRole exist in the table in both databases?
_________________
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 |
|
|
Bastiaan Molsbeck
Joined: 26 Mar 2010
Posts: 38
Location: The Netherlands
|
| Posted: Tue Nov 27, 2012 4:04 pm Post subject: |
|
|
Yes, the database schema (tables, constraints, indexes, views, triggers, etc.) are exactly the same of both databases.
Only the data differs. |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6345
Location: Red Gate Software
|
| Posted: Tue Nov 27, 2012 4:42 pm Post subject: |
|
|
If I use the following simplified code, the unique constraint is dropped and should prevent the error.
| Code: |
Imports RedGate.SQLCompare.Engine
Imports RedGate.SQLCompare.Engine.ReadFromFolder
Imports RedGate.SQLDataCompare.Engine
Imports RedGate.Shared.SQL.ExecutionBlock
Module Module1
Sub Main()
Dim d As New Database
Dim d2 As New Database
Dim cp1 As New ConnectionProperties("localhost", "SDCTest1")
Dim cp2 As New ConnectionProperties("localhost", "SDCTest2")
d.RegisterForDataCompare(cp1)
d2.RegisterForDataCompare(cp2)
Dim opts As New EngineDataCompareOptions(MappingOptions.Default, ComparisonOptions.Default, SqlOptions.Default Or SqlOptions.DropConstraintsAndIndexes)
Dim sess As New ComparisonSession
sess.Options = opts
Dim mappings As New TableMappings
mappings.Options = opts
mappings.CreateMappings(d.Tables, d2.Tables)
For Each m As TableMapping In mappings
m.Include = True
Next
Dim w As New Work
sess.CompareDatabases(d, d2, mappings)
Dim prov As New SqlProvider
prov.Options = opts
Dim eb As ExecutionBlock = prov.GetMigrationSQL(sess, True)
Dim sqlcode As String = eb.GetString()
Console.WriteLine(sqlcode)
End Sub
End Module
|
_________________
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 |
|
|
Bastiaan Molsbeck
Joined: 26 Mar 2010
Posts: 38
Location: The Netherlands
|
| Posted: Tue Nov 27, 2012 4:57 pm Post subject: |
|
|
Hi, my code is nearly the same, except that in my case the "mappings" object is declared as "SchemaMappings" instead of "TableMappings".
Could this be the reason? Could you test this?
The reason why I use "SchemaMappings" is that the method "SQLDataCompare.Engine.DataCompareUserActions.ReplayUserActions" requires this object.
I cannot convert a "TableMappings" to a "SchemaMappings" object to pass to this method.
And I use the "ReplayUserActions" method, because some tables have to be excluded, and some tables have a WHERE clause to exclude some rows.
(Sidenote for this case: the table "tbdRole" is not excluded and does not have a WHERE clause). |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6345
Location: Red Gate Software
|
| Posted: Tue Nov 27, 2012 5:43 pm Post subject: |
|
|
Far as I know, "SchemaMappings" refers explicitly to objects of type "Schema", ie if you wanted to map DBO to SCHEMA1 to some other schema -- it would not map any of the child objects of that schema, though.
_________________
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 |
|
|
Bastiaan Molsbeck
Joined: 26 Mar 2010
Posts: 38
Location: The Netherlands
|
| Posted: Tue Nov 27, 2012 8:42 pm Post subject: |
|
|
Hm, your online help tells something different; the SchemaMappings is described as follows:
"Holds mappings for schemas, users, or roles. Uses the database objects to create the mappings for the views and the tables from the two databases."
http://help.red-gate.com/help/SQLDataCompareAPIv6/1/en/html/N_RedGate_SQLDataCompare_Engine.htm
Anyway, all I want to achieve is that the SqlOption "DropConstraintsAndIndexes" actually does drop and recreate the unique constraint.
The online help page about this describes the following:
DropConstraintsAndIndexes - Drop and recreate primary keys, indexes, and unique constraints in the synchronization script. If the primary key, index, or unique constraint is the comparison key, it cannot be dropped.
http://help.red-gate.com/help/SQLDataCompareAPIv6/1/en/html/T_RedGate_SQLDataCompare_Engine_SqlOptions.htm
On the concerning table, the unique constraint is NOT used as the comparison key, so it should be dropped, but it isn't. Sounds like a bug to me.
What do you suggest that I should modify in my code? |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6345
Location: Red Gate Software
|
| Posted: Wed Nov 28, 2012 10:29 am Post subject: |
|
|
I'm sorry, I just do not know. I am working off the same documentation as you. I know that in my example, I do not use SchemaMappings and it's fine.
I'm turning this support issue over to the development team.
_________________
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: 6345
Location: Red Gate Software
|
| Posted: Wed Nov 28, 2012 10:33 am Post subject: |
|
|
I'd also like to point out, the doc link you have is for API v6.
_________________
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 |
|
|
|
