Devart Forums

Hi,

it would be a great enhancement, when dbForge for Oracle would support "indirect" support foreign keys.

Example:
I have three tables SRC, TRGT and SRC_TRGT, where SRC_TRGT is a n-m-relation between SRC and TRGT.

SRC and TRGT are configured with unique key column IDENT (varchar2) in addition to the primary key (number).

The comparison in SRC_TRGT should then recognize the foreign key constraints and should compare IDENT of the foreign tables instead of primary key.

Did you try to adjust table mapping?
You can select any unique key instead of primary one as a comparison key.

Hello Duke,

Duke wrote:Did you try to adjust table mapping?
You can select any unique key instead of primary one as a comparison key.

Yes, I did adjust the table mapping, but this does only work for the table of the primary key. Foreign key columns are compared by value, not by the selected key columns of the foreign table. Is it clear now?

I'm sorry but it's not clear yet.

Could you please describe the goal you want to achieve in terms of end-user tasks?

It's not quite clear what data you want to compare and what will you do with results.

A small example (with data) would be great.

Hi,

Duke wrote:I'm sorry but it's not clear yet.
Could you please describe the goal you want to achieve in terms of end-user tasks?
It's not quite clear what data you want to compare and what will you do with results.
A small example (with data) would be great.

The goal is to compare two schemas without depending on primary keys of the tables. For "simple" tables we can use the key mapping feature, but for tables containing foreign keys this is not possible.

Example schema:

We have three tables:
- MASTER_TAB: MASTER_ID NUMBER (PK), IDENT VARCHAR2(50), ...
UK(IDENT)
- CHILD_TAB: CHILD_ID NUMBER (PK), IDENT VARCHAR2(50), ...
UK(IDENT)
- CROSS_TAB: CROSS_ID NUMBER (PK), MASTER_ID (FK), CHILD_ID (FK)
UK(MASTER_ID,CHILD_ID)

- The IDENT-columns are used as key columns for MASTER_TAB and CHILD_TAB (unique key).
- The PKs are filled automatically by sequences.
- When comparing two schemas, the PKs in MASTER_TAB and CHILD_TAB can differ, IDENT is used to identify the record.

When comparing CROSS_TAB, dbForge should not compare the PKs of MASTER_TAB and CHILD_TAB. Since IDENT is configured as key for these tables, the PKs should get "mapped" to the respective IDENTs.
In the moment you will not be able to sync CROSS_TAB, when PKs in MASTER_TAB and CHILD_TAB differ.

Example data:

SCHEMA_A contains the records:
MASTER_TAB:
1,'TEST'
2,'ANOTHER TEST'

CHILD_TAB:
1,'TEST'
2,'ANOTHER TEST'

CROSS_TAB:
1, 1, 1
2, 1, 2

And SCHEMA_B contains the records:
MASTER_TAB:
10,'TEST'
20,'ANOTHER TEST'

CHILD_TAB:
100,'TEST'
200,'ANOTHER TEST'

CROSS_TAB:
1, 10, 100
2, 10, 200

With this data, all data should be considered equal, in the moment all records in CROSS_TAB are considered different.

This case hardly could be automated, because program won't know what values to take instead of MASTER_ID, CHILD_ID values in CROSS_TAB.

But if you create CROSS_TAB to reference unique keys (IDENT column) in MASTER_TAB and CHILD_TAB instead of primary keys you'll be able to compare these three tables in different databases. All that is required in this case is to exclude primary key columns from comparison.

Hi Duke,

Duke wrote:This case hardly could be automated, because program won't know what values to take instead of MASTER_ID, CHILD_ID values in CROSS_TAB.

I understand that there is no way to automate this behavior in common, but If I configure MASTER_TAB and CHILD_TAB in dbForge to use IDENT as key, it should not be a problem to "resolve" foreign keys in other tables referencing MASTER_TAB or CHILD_TAB, or am I missing something?

No, the program won't do it automatically, I meant changing your database schema.

If you change comparison key for a pair of tables this does not affect dependent tables.
In your example data in CROSS_TAB remains different.

We are about to release the version 2.0 of Data Compare for Oracle.
In this version there is a workaround for comparing data in CROSS_TAB without changing the main schema. You can create a view (e.g. vCROSS_TAB) referencing IDENT columns instead of IDs. Then you compare the views and see that data is equal.