DSXchange

Hi

I was trying to load data in oracle table called ITEMS from sequintial file ,though the job runs it does not load data into the table

When I see the event detail it says
Tutorial3..Transformer_2.DSLink4: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO ITEMS(ORDID, ITEMID, PRODID, ACTUALPRICE, QTY, ITEMTOT) VALUES (?,?,?,?,?,?)
SQLSTATE=23000, DBMS.CODE=2291
[DataStage][SQL Client][ODBC][Oracle][ODBC][Ora]ORA-02291: integrity constraint (SCOTT.ITEM_FOREIGN_KEY) violated - parent key not found

Can somebody plz explain what went wrong ?

Thanks

devsonali wrote:integrity constraint (SCOTT.ITEM_FOREIGN_KEY) violated - parent key not found

You have it right there..... Violation of foreign key constraint.

ITEMID value supplied does not occur on the ITEMS table.

Ah... the dreaded SCOTT schema. Someone is practicing, me thinks.

Ray

ITEMID value supplied does not occur on the ITEMS table.

Can you expand more on this what does it mean??and how should I fix this .
what does it violation of foreign key constraint mean?

plz explain

Thanks

Actually, it was a guess on his part. There's not enough information to know for sure. And it's not about the ITEMS table directly, but rather some value in the record who's field is part of the noted FK constraint does not exist in the table being constrained against. A different table.

For example, if it is indeed ITEMID (still just a guess, it looks like it could be any of the first three fields) then the value in the record you are trying to insert (i.e. the 'child' record) does not exist in some sort of ITEM_MASTER (i.e. 'parent' record) table. Children need parents, you know. You'd have to look at the constraint definition to know for sure. Toad works well for this.

Talk to your DBA, this is all basic relational database stuff. I'm sure they'd be happy to enlighten you.

An educated guess. There's a foreign key constraint called ITEM_FOREIGN_KEY and a column in the insert statement called ITEMID.

Hi This is the script that was run to make Item table
--assume that orders table also exits

CREATE TABLE ITEMS
(
ORDID number(4) not null,
constraint ITEM_FOREIGN_KEY foreign key (ORDID) references ORDERS (ORDID),
ITEMID number(4) not null,
constraint ITEM_PRIMARY_KEY primary key (ORDID, ITEMID),
PRODID number(6) null,
ACTUALPRICE number(8,2) null,
QTY number(8) null,
ITEMTOT number(8,2) null
);

Does this help to get the solution ?

Thanks

Shri - it does. You should look up the "foreign key" concept in the database book (or web page) of your choice. Once you understand what you executed in the DDL you will understand what the error message means and how to correct it.

ArndW wrote:Shri - it does. You should look up the "foreign key" concept in the database book (or web page) of your choice. Once you understand what you executed in the DDL you will understand what the error message means and how to correct it.

I understand the concept of foreign key but I am not able to understand the error ? and how to fix it ??

Do you know the data values for the failed statement? Please go into your database command line tool and issue the same insert statement as generated in the DataStage job.

You will get the same error.

Then list your ITEMS table and you will see that your ITEMID does not exist in that table. Then please re-read the manual on foreign keys and constraints, as I think that you might not have fully understood the implications of using them in a database.

My guess was slightly astray. In fact your foreign key references ORDID on the ORDERS table. Therefore your INSERT statement for the ITEMS table appears to contain an ORDID value that does not exist on the ORDERS table.

The only "solution" is not to insert any row into the ITEMS table for which the value of ORDID is absent from the ORDERS table.

You can alter your job to check for this situation by performing a lookup.

ray.wurlod wrote:My guess was slightly astray. In fact your foreign key references ORDID on the ORDERS table. Therefore your INSERT statement for the ITEMS table appears to contain an ORDID value that does not exist on the ORDERS table.

AHA!

But only slightly.