Comparing Related Tables With Identity Primary Keys
christakis.sofroniou
Posts: 2
I'm using Data Compare 8 as part of a test process for an application that populates a database. The application, after running a test, should have populated this database in a certain deterministic way and so we have a set of results in an 'expected results' database that we can compare to i.e. if the test runs correctly the application database should look like the expected results database. All good so far.
The problem I'm having, and wondering whether SQL Data Compare can solve, is that some primary keys are not deterministic because they are identity columns and the we don't know the order in which the application will insert in these tables. If this identity column is used as a foreign key in another table, is there a way I compare this foreign key relationship is correct by looking at specific, non-identity column, fields in the related table in a similar way that we can compare a table using the comparison key?
E.g. Table tblA has an identity column, colA, which is a PK, and a second column, colB, which is unique. tblB has a foreign key, colA, taken from tblA.colA. Is there a way I can compare tblB ignoring the fact that the tblB.colA may be different between the two databases, but ensuring that tblA.colB in the related tblA record is the same?
The problem I'm having, and wondering whether SQL Data Compare can solve, is that some primary keys are not deterministic because they are identity columns and the we don't know the order in which the application will insert in these tables. If this identity column is used as a foreign key in another table, is there a way I compare this foreign key relationship is correct by looking at specific, non-identity column, fields in the related table in a similar way that we can compare a table using the comparison key?
E.g. Table tblA has an identity column, colA, which is a PK, and a second column, colB, which is unique. tblB has a foreign key, colA, taken from tblA.colA. Is there a way I can compare tblB ignoring the fact that the tblB.colA may be different between the two databases, but ensuring that tblA.colB in the related tblA record is the same?
