Red Gate forums :: View topic - Cannot update foreign keys during synch
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Compare Previous Versions
SQL Data Compare Previous Versions forum

Cannot update foreign keys during synch

Search in SQL Data Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
Ross Beehler



Joined: 20 Jan 2009
Posts: 1

PostPosted: Tue Jan 20, 2009 9:42 pm    Post subject: Cannot update foreign keys during synch Reply with quote

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
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Wed Jan 28, 2009 3:20 pm    Post subject: Reply with quote

Thanks for your post, and sorry for the slow response. We have been experiencing very high volumes of calls, and have not been able to address the forums as quickly as we would have liked.

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

 
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