ODI Reverse Engineer Does Not Correctly Import Foreign Keys to a Different Schema (Doc ID 2631074.1)

Last updated on AUGUST 10, 2023

Applies to:

Symptoms

An Oracle schema is reverse engineered in Oracle Data Integrator (ODI) 12.1.3 or higher with either standard reverse engineering or customized reverse engineering using RKM Oracle. For tables that contain foreign keys, it was noted that the models contain the foreign keys pointing to tables from the same schema, but the foreign keys pointing to a different schema are not included.

Example of setup:
* TABLE_A in SCHEMA_A 
* It has a Foreign key FK_B pointing to TABLE_B in SCHEMA_A. This works correctly. The foreign key is shown under the Model in the Datastore. Click 'Expand Constraints' to see the keys there.
* It has another Foreign Key FK_C pointing to TABLE_C in SCHEMA_B. This cannot be seen in the constraints list under the Datastore.

The issue can be reproduced at will with the following steps:
1. Create two tables in a schema and another one in a different schema.
2. Link the tables with foreign keys.
3. After reverse engineer in ODI, verify if the inter-schema foreign keys are present.

Cause