What are the challenges you face when working across database platforms? Take the survey

How to use parent tables to compare child tables that have different unique id's?

This is a common problem for us when using Data Compare. Parent table has unique constraint on a char field alongside an identity field which also serves as a one-to-many foreign key in the child table. The child table uses an identity field to distinguish each row but there's no guarantee the id's will be the same across databases so only the parent can be matched but the child table only has the parent_id identity fk which is not a viable comparison key.
John D. Shkolnik
VP, Product Management | Managed Services & Products
Deloitte Risk and Financial Advisory
Deloitte & Touche LLP

Best Answer

  • Options
    sam.blackburnsam.blackburn Posts: 224 Gold 2
    You'll probably need to create a view (in each database) joining the two tables, enable the "include views" option and select a comparison key using the Tables & Views tab. If you want to deploy data, you might also need to create an insert trigger on the view.
    Software Developer
    Redgate Software


Sign In or Register to comment.