Informatica and Self-Referential foreign keys

There are always a few gotchas and bugs you’ll come across when you work with automatically generated code.

Here’s one (Bug, in my opinion) that you’ll come across when you work with Informatica Power Center when your source table has self-referential foreign keys.

As you probably know, self referential foreign keys are those when a column in a table references another column in the same table for the parent key. The employee table in Oracle’s Scott schema is a prefect and simple example. The manager ID for a given employee should in turn be an existing employee.

For the table creation script with the foreign key and other constraints, check this link. If you have the default tables created, then you can alter your table to add the constraint as below.

Let’s create a target table with the same structure as the source, create a mapping, session and workflow and load data into target.

The log file is as expected if you do not make any further modifications to the source qualifier. It does not have any where clause as there is no need for one.

Here’s the interesting part.. If you open the source Qualifier and click “generate query”, you’ll see the following query generated.

So, If you are making any changes to the Source Qualifier, make sure you remove this condition (WHERE
SCOTT_EMP.EMPNO=SCOTT_EMP.MGR
) in addition to the changes that you are making. Otherwise it will result in zero rows being passed on to the next transformation.

As to why this invalid query is generated, I am guessing Informatica generates the query by joining all the tables using the columns involved in all the parent-child relationships. In this case, since the parent and the child table are the same,

a) you need to include either two instances of scott_emp if you need to get the manager’s details

or

b)not add any condition at all : if you need only the employee’s details.

Seems like a minor bug that will be fixed soon enough. I don’t have access to Informatica 9 yet, but I’ll check on this and post an edit if it is fixed in the new release.

Hope this helps..
Rajesh.

  • Saurabh

    That is a real catcher!!!
    Great job.

    Cheers.