Cannot update foreign keys during synch
Ross Beehler
Posts: 2
We want to use SQL Data Compare to promote staged configurations from a test environment to a production environment (i.e. never really synchronize ... always push data). Such a model will have to ignore identity columns on the production environment, which is simple to do by to configuring my project to compare the logical key column(s) and to not 'Include identity columns'. However, where this breaks down is that Data Compare will not update foreign keys for associated data that gets promoted at the same time.
For example, I have a HEADER and DETAIL table such as:
CREATE TABLE HEADER (
OBJECT_ID int PRIMARY KEY IDENTITY(1,1),
NAME NVARCHAR(255) NOT NULL) -- assume this is the logical key
CREATE TABLE DETAIL (
OBJECT_ID int PRIMARY KEY IDENTITY(1,1),
HEADER_ID int NOT NULL, -- assume this is a foreign key to HEADER.OBJECTID
DATA NVARCHAR(255))
If both test and production have HEADERs that share the same OBJECT_ID but have different NAME values, we can configure Data Compare to properly promote the test record with a new OBJECT_ID, but when it goes to promote any new DETAILs that are associated with that test HEADER, it inserts them in the production environment with the original HEADER_ID value, and not the new OBJECT_ID of the HEADER in the production environment.
Am I missing some option that will evaluate the foreign key constraints of the data you're promoting, arrange resulting SQL in order of dependency, capture new identities on insert, and set foreign key values when inserting 'child' records. Right now it looks to only ever process tables in alphabetical order.
I do understand there are other workarounds with compound keys or by using ranges of identity values, but both options have their problems. For example, ORMs like NHibernate greatly discourage against using compound keys and identity ranges aren't fun to maintain and will eventually fail.
As I understand it, my only option is to develop this functionality myself using the SQL Compare SDK ... at least until this is supported in SQL Data Compare.
Any other thoughts/suggestions are appreciated.
Ross
For example, I have a HEADER and DETAIL table such as:
CREATE TABLE HEADER (
OBJECT_ID int PRIMARY KEY IDENTITY(1,1),
NAME NVARCHAR(255) NOT NULL) -- assume this is the logical key
CREATE TABLE DETAIL (
OBJECT_ID int PRIMARY KEY IDENTITY(1,1),
HEADER_ID int NOT NULL, -- assume this is a foreign key to HEADER.OBJECTID
DATA NVARCHAR(255))
If both test and production have HEADERs that share the same OBJECT_ID but have different NAME values, we can configure Data Compare to properly promote the test record with a new OBJECT_ID, but when it goes to promote any new DETAILs that are associated with that test HEADER, it inserts them in the production environment with the original HEADER_ID value, and not the new OBJECT_ID of the HEADER in the production environment.
Am I missing some option that will evaluate the foreign key constraints of the data you're promoting, arrange resulting SQL in order of dependency, capture new identities on insert, and set foreign key values when inserting 'child' records. Right now it looks to only ever process tables in alphabetical order.
I do understand there are other workarounds with compound keys or by using ranges of identity values, but both options have their problems. For example, ORMs like NHibernate greatly discourage against using compound keys and identity ranges aren't fun to maintain and will eventually fail.
As I understand it, my only option is to develop this functionality myself using the SQL Compare SDK ... at least until this is supported in SQL Data Compare.
Any other thoughts/suggestions are appreciated.
Ross
Comments
I recreated your issue using the table structures you supplied, and this is actually a known issue that our developers are working on. As you have found, the foreign key values are inserted as they are in the source, and are not based on the new values of OBJECT_ID. For your reference the bug tracking code for this issue is SDC-889.
Hopefully this will be fixed in the next version, but currently the bug has not been assigned a fix version, so I can't tell you exactly if/when the bug will be fixed.