Topic: SQL*Plus and PL/SQL >> ORA-02291: integrity constraint violated - parent key not found
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: ORA-02291: integrity constraint violated - parent key not found |
| pora |
Posted: Jul 16, 2007 12:51:28 PM |
Total Post: 7
Joined: Mar, 2007
|
Hi,
I am running following INSERT and getting parent key not found error.
ERROR at line 1:
ORA-02291: integrity constraint (O_ITEM_REL_FK1) violated - parent key not found
o_item (parent) and o_item_REL (child) has parent child relationship.
Both id i ma generating through sequence and I have to load id vlaues in both table same time means when i generate sequence in o_item table then same value i have to load in o_item_REL table. It was running fine and suddenly it's throwing this error.
INSERT ALL
INTO o_item
(o_item_id, PROD_ID, PRICE, L_Total, Q_ORDER, Q_SHIP, L_NUM
)
VALUES
('LD_'||o_item_seq.NEXTVAL, PRODUCT_ID, PRICE, L_Total, Q_ORDER, Q_SHIP, L_NUM
)
INTO o_item_REL
(O_ID,
o_item_id
)
VALUES (o_id,
'LD_'||o_item_seq.CURRVAL
)
SELECT A.PROD_ID, D.O_id, A.PRICE, A.L_Total, A.Q_ORDER, A.Q_SHIP, A.L_NUM
FROM o_load A,
O_item C,
o_item_REL B,
ord D
WHERE A.prod_id = C.prod_id
AND A.j_ref_num = C.n_ord_id
AND A.n_ord_id = D.n_ord_id
AND C.o_item_id = B.o_item_id
AND (A.N_ORD_ID not in (select F.n_ord_id from o_item F
where F.n_ord_id = a.n_ord_id
)
OR (A.N_ORD_ID in (select F.n_ord_id from o_item F
where F.n_ord_id = a.n_ord_id
)
AND A.prod_id not in (select F.prod_id from o_item F
where F.n_ord_id = a.n_ord_id
)
)
)
/
Please let me know, appreciated.
Thanks,
pora
|
|
|
 pora |
| Posted: Jul 17, 2007 03:43:36 PM |  |
|
Total Post: 7
Joined: Mar, 2007
|
If anyone has better idea, awaiting for response.
I am using 9i r2 and running this INSERT ALL sql from almost two years and getting error first time.
Thanks,
|
|
|
|
|
| pora |
| Posted: Jul 18, 2007 02:15:59 AM | |
|
Total Post: 7
Joined: Mar, 2007
|
Does it bug in Oracle 9i for Multi Insert? which order of the tables into which Oracle inserts data is not determinate?
I am using 9i R2 and we have also recently applied patches.
thanks,
|
|
|
|
|
| Nehru |
| Posted: Jul 26, 2007 07:36:25 AM | |
|
Total Post: 46
Joined: Jul, 2007
|
Hi,
Just as per my Knowledge at the time of insertion
The foreign key value has no matching primary key value.
Better to check SEQUENCE values before inserting into both the tables
Take Care
Bye
Nehru
|
|
|
|
|
| pora |
| Posted: Jul 30, 2007 04:49:49 PM | |
|
Total Post: 7
Joined: Mar, 2007
|
Thanks.
Looks like its a known issue in Oracle for Multi Insert. When its attempting few more records in a batch job, it throws that error as Multi Insert doesn't insert the reocrds sometimes in a table order.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 335 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
