Integrity constraint violated - parent key not found

[vinaymanchinila]

Hi,
trying to load one of the fact table and I get an warning which leads to the row not being loaded into the table.

Fact: ORA-02291: integrity constraint (COGNOS.FACTTABLE_FK27) violated - parent key not found


Is there a workaround for this.

Thanks,

[ketfos]

Hi,
You can put a condition in the trransformer to reject those records which will violate this constraint and write it to a seperate file or table for your investigation.
Or
You can temporaryly disable your constraint on the table before loading.

Thanks
Ketfos

[sjacobk]

I feel you need to find out why data is not there in the parent table. The Constraints are defined in the table to ensure data integrity. If you miss records as the result of an integrity constraint violation, I feel we need to find out why the required data is not found in the parent table. If the occurrence is very rare, probably u can take it lightly. But if it is frequent u are missing something in your process..

[Sainath.Srinivasan]

By the name of the file - Fact Table - I assume that the DS logic must ensure that the dimension keys are looked up before loading. Also in this stage, you must ensure the data quality and have minimal (or zero) reject rows.

[edward_m]

Hi,
You can put a condition in the trransformer to reject those records which will violate this constraint and write it to a seperate file or table for your investigation.
Or
You can temporaryly disable your constraint on the table before loading.

Thanks
Ketfos

How do we put a constraint in transformer for insert into the table without parent key voilation and reject into reject file for parent key violated records.

[chulett]

A couple of different ways...

If you are loading them seperately, parents first in one job and then children in the next and you are hashing up the parent keys - a miss on the parent hash would indicate the child would generate the error if sent to the database. Use that information to logically reject the record.

Or send the row to the database regardless and check for a physical reject. Send it down a later dedicated rejection row by checking the result of the insert. The constraint could look like:

Code: Select all

LinkName.REJECTEDCODE <> 0

This would capture any rows that went down the named link and were rejected by the database. Or:

Code: Select all

LinkName.REJECTED

A boolean that would capture any rows that did not go down the previous named think for any reason.

[haimurali]

I am using two jobs to load two DB2 tables A and B. A's primary key is forein key in B.

After loading A, while loading B I am not getting any warnings when there is a viloation of referential integrity. Only the records were not loaded/ dropped. The job was successful WITHOUT warnings.

Is there a setting I need to do get the warnings in case of Referential integrity viloation when using DB2 UDB.