Update tables with a unreliable foreign key

tadhgtadhg Posts: 3
Sorry if this has been asked but I'm struggling to find the answer.

Using SQL Data Compare I wish to update a table but I cannot guarantee the same value foreign key without looking at the foreign table. Say for example I have two tables:

Table1
ID Name
1   Donkey
2   Elephant

Table2
ID FK_ID Name
1   1        Carrots
2   2        Peanuts

Where FK_ID is pointing at Table1

The SQL script I'm generating from DataCompare looks something like

INSERT INTO Table2 (FK_ID,Name) VALUES (1,'Carrots') ... etc

What I'm concerned about is if my target database has Table1 has values as such:

Table1
ID Name
1   Elephant
2   Donkey
Then Carrots will be linked to Elephant instead of Donkey (although I'm sure the Elephant would not mind :-P).

Is there a way to tell Data Compare to look at the 'Name' column of Table1 to ensure that the correct FK_ID is maintained?

Comments

  • Hi Tadhg,

    There's not really a way to do exactly this in SQL Data Compare - I guess we'd recommend that you first update Table1 from the source database so that you can be sure to maintain referential integrity?

    Alternatively, you could create a view containing all the important fields in both databases and compare that? Do either of those options sound practicable in your environment?
    Andy Campbell Smith

    Red Gate Technical Support Engineer
  • The columns are already named and embedded across mutliple environments so updating tables is a big task.

    I like the View suggestion. I will investigate that idea.
  • OK - let me know how you get on!
    Andy Campbell Smith

    Red Gate Technical Support Engineer
Sign In or Register to comment.