To reject the records that violates Referential Integrity

[laxman.ds]

My Parallel job is getting aborted with "ORA:-02291-Parent Key Not found" error while inserting into the table.This error is since i am inserting the values into a column which is defined as a foriegn key of other column.If the values am inserting does not match the values of the referenced column i am getting this error.I want the job to get finished by rejecting the records which violates the referential integrity.Please provide a solution asap.

[Maveric]

How are you inserting the data into the table? ODBC, oracle enterprise stage, etc. load, upsert?

[laxman.ds]

How are you inserting the data into the table? ODBC, oracle enterprise stage, etc. load, upsert?

i am inserting using Oracle Enterprise stage,Upsert option.i am disabling the update by using false where clause like 1=2.

[Maveric]

For the upsert mode property, you can use "update only option" and give the insert statement. Also you can set the Output Reject Record to true and capture them from the reject link. Might work. Try it and let us know if it works.

[gateleys]

Please provide a solution asap.

Most DSXchange members will find this statement rude, even though it starts with a 'please'. Yeah, they will tell you that "we don't do asap here. If it is urgent, then someone may oblige if an appropriate fee is paid".

To answer your question, I think it is a bad design that you have rows that violate foreign key constraints. Such rows should never have made it to the target. However, if you are handling late arriving dimensions, then there are special ways of handling it.

[laxman.ds]

For the upsert mode property, you can use "update only option" and give the insert statement. Also you can set the Output Reject Record to true and capture them from the reject link. Might work. Try it and let us know if it works.

I tried this way,but the job is getting aborted with the same error.The Error is as following:

TgtPartyInd,0: Open failed for update:
update is: INSERT
INTO
CCRPROD.PARTY_IND
(DATE_OF_BIRTH, GENDER_CD, MARITAL_STATUS_CD, EDUCATION_CD, PARTY_ID, ETL_CRT_DATE, ETL_CRT_JOB)
VALUES
( :DATE_OF_BIRTH, :GENDER_CD, :MARITAL_STATUS_CD, :EDUCATION_CD, :PARTY_ID, :ETL_CRT_DATE, :ETL_CRT_JOB)
sqlcode is: -2291
esql complaint: ORA-02291: integrity constraint (CCRPROD.SYS_C002996) violated - parent key not found

[laxman.ds]

To answer your question, I think it is a bad design that you have rows that violate foreign key constraints. Such rows should never have made it to the target. However, if you are handling late arriving dimensions, then there are special ways of handling it.[/quote]
Could you help me with a special way for handling it?

[Maveric]

Do a lookup with the reference table, on the foreign key and reject data that does not have a match in the reference table. However i dont know what "Late arriving dimensions" are.

[laxman.ds]

This is not a bright option since i have 7,8 foriegn keys in my table.is there any option to reject the records that does not meet the foriegn key constraint?

[mdan]

What about setting the option Output Reject Records to true?

Here is the link with the solution to your issue: (Vincent's blog) [http://wiki.ittoolbox.com/index.php/Par ... led_upsert.]

This is not a bright option since i have 7,8 foriegn keys in my table.is there any option to reject the records that does not meet the foriegn key constraint?

[gateleys]

This is not a bright option since i have 7,8 foriegn keys in my table.is there any option to reject the records that does not meet the foriegn key constraint?

1. Get the foreign keys from your target, concatenate them and compute CRC on the concatenated value.
2. Populate a hashed file with this CRC value as the key.
3. Repeat steps 1 and 2 with your input rows as well.
4. Perform a lookup based on the CRC value, and pass only those rows to target if the lookup returns a match, else pass the row via a reject link to a reject file.

[ray.wurlod]

If you don't have a CRC routine, you can use one of the change detection stage types.