Event detail Warning
Moderators: chulett, rschirm, roy
Event detail Warning
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
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
Re: Event detail Warning
You have it right there..... Violation of foreign key constraint.devsonali wrote:integrity constraint (SCOTT.ITEM_FOREIGN_KEY) violated - parent key not found
gateleys
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
--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
Last edited by devsonali on Thu Jun 14, 2007 7:48 pm, edited 1 time in total.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
I understand the concept of foreign key but I am not able to understand the error ? and how to fix 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.
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
AHA!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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: