How can we help you today? How can we help you today?

Update tables with a unreliable foreign key

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?
tadhg
0

Comments

3 comments

Sort by
  • Andy C S
    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 C S
    0
  • tadhg
    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.
    tadhg
    0
  • Andy C S
    OK - let me know how you get on!
    Andy C S
    0

Add comment

Please sign in to leave a comment.