Home » SQL & PL/SQL » SQL & PL/SQL » ORA-02291: integrity constraint violated - parent key not found
ORA-02291: integrity constraint violated - parent key not found [message #251968] Tue, 17 July 2007 10:20 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
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 am generating through sequence and I have to load id values 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.
My where clause running fine and without child table insert it's also running fine. I have also tried to load data into dev from prod but it also throwing error in development.

SQL
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, Z.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)
       )
    )
/
commit;


Please let me know, appreciated.

Thanks,

[Updated on: Tue, 17 July 2007 10:40] by Moderator

Report message to a moderator

Re: ORA-02291: integrity constraint violated - parent key not found [message #251984 is a reply to message #251968] Tue, 17 July 2007 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In case of INSERT ALL statement, I'm not sure Oracle guarantees the order of inserts is the one you give (it just guarantees all are executed) but till now I didn't come to an example that explicitly shows this.

Regards
Michel
Re: ORA-02291: integrity constraint violated - parent key not found [message #252060 is a reply to message #251984] Tue, 17 July 2007 14:05 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks, I was running for atleast two years and getting error first time. If i am inserting sequence (which FK causing problem) in that column and i don't understand why it's complaining as i am inserting nextval of sequence in parent table and same current sequence reading and inserting into child table.

Thanks,
Re: ORA-02291: integrity constraint violated - parent key not found [message #252064 is a reply to message #252060] Tue, 17 July 2007 14:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe sometimes it executes the second "INTO" before the first one.
So parent does not exist before child is inserted.

You can workaround this with a deferred constraint.

By the way, it does not matter you use currval in one INTO if there is nextval in another one, and the order does not either matter. Oracle calculates the sequence value only once per row from the select:
SQL> drop sequence s1;

Sequence dropped.

SQL> drop sequence s2;

Sequence dropped.

SQL> create table p (id1 integer, id2 integer);

Table created.

SQL> create table c (id1 integer, id2 integer);

Table created.

SQL> create sequence s1;

Sequence created.

SQL> create sequence s2;

Sequence created.

SQL> insert all 
  2  into c values (s1.currval, s2.nextval)
  3  into p values (s1.nextval, s2.nextval)
  4  select 1 from dual connect by level <= 3
  5  /

6 rows created.

SQL> select * from p;
       ID1        ID2
---------- ----------
         1          1
         2          2
         3          3

3 rows selected.

SQL> select * from c;
       ID1        ID2
---------- ----------
         1          1
         2          2
         3          3

3 rows selected.

Regards
Michel
Re: ORA-02291: integrity constraint violated - parent key not found [message #252073 is a reply to message #252064] Tue, 17 July 2007 14:48 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member

Thanks Micheal.
You are right but i have this in production so constraint differ is ok? Do you think that it causing due to existing data or due to only sequence means with new insert?
I have loaded same data into my other user table and also recreated sequence but it's throwing same error.
Don't know why this happening?????

Thanks,
[/CODE]
Re: ORA-02291: integrity constraint violated - parent key not found [message #252079 is a reply to message #252073] Tue, 17 July 2007 15:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not because of data, it just because you can't know in which are done the insert.
I don't see any other solution than deferred constraint.
You can deferred the constraint just for your transaction.
SQL> create table p (pid integer primary key, val integer);

Table created.

SQL> create table c (
  2    cid integer primary key, 
  3    pid integer,
  4    constraint c_p_fk foreign key(pid) references p 
  5      deferrable initially immediate);

Table created.

SQL> create sequence s1;

Sequence created.

SQL> create sequence s2;

Sequence created.

SQL> set constraint c_p_fk deferred;

Constraint set.

SQL> insert all 
  2  into p values (s1.nextval, 0)
  3  into c values (s2.nextval, s1.currval)
  4  select 1 from dual connect by level <= 3
  5  /

6 rows created.

SQL> set constraints all immediate;

Constraint set.
...

The "initialy immediate" indicates that the default behaviour is "not deferred" so other see the constraint as normal one.
But your transaction can defer it for itself till commit or resetting the constraint to immediate as in my example.

Regards
Michel
Re: ORA-02291: integrity constraint violated - parent key not found [message #252167 is a reply to message #252079] Wed, 18 July 2007 01:13 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
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,

Re: ORA-02291: integrity constraint violated - parent key not found [message #252171 is a reply to message #252167] Wed, 18 July 2007 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle never says they are done in the order you give them, it just says (afaik) they are all done.
So it is not a bug.
But you can open a SR to have a complete and accurate information from Oracle.
If so please, tell us.

Regards
Michel
Re: ORA-02291: integrity constraint violated - parent key not found [message #252313 is a reply to message #252171] Wed, 18 July 2007 10:32 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks, I read somewhere in other forums that insert all" is not atomic after all. I am just looking alternate method like trigger or pl/sql if you guys have any better solution but i am getting this error.
This issue is an open bug:
Quote:
<bug:2891576> MULTI TABLE INSERT (INSERT ALL) FAILS WITH ORA-2291

Moderator: Due to copyright restriction, the rest of the quoting is deleted. Please refer to the original document.


Thanks for your help.

[Updated on: Thu, 19 July 2007 03:11] by Moderator

Report message to a moderator

Re: ORA-02291: integrity constraint violated - parent key not found [message #252315 is a reply to message #251968] Wed, 18 July 2007 10:37 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Just curious, why don't you like the idea of deferred constraints?

You certainly could do it with a trigger, although I personally don't like doing inserts within triggers unless absolutely necessary.



Re: ORA-02291: integrity constraint violated - parent key not found [message #252319 is a reply to message #252313] Wed, 18 July 2007 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I read somewhere in other forums that insert all" is not atomic

Where do you find this?
I'm very curious to see that and if there is a test case.
Afaik, Oracle guarantees that any DML statement is atomic. Even more it is consistent with a point in time.

Glad to see that Metalink and I say the same thing.

Regards
Michel

Re: ORA-02291: integrity constraint violated - parent key not found [message #252348 is a reply to message #252315] Wed, 18 July 2007 12:27 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks, I will try to use and let you know.
Just kind of hasitate if during commit, it will let me commit or not?
Re: ORA-02291: integrity constraint violated - parent key not found [message #252355 is a reply to message #251968] Wed, 18 July 2007 12:39 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Just kind of hasitate if during commit, it will let me commit or not?
This is what TEST systems & TEST databases are for!
Re: ORA-02291: integrity constraint violated - parent key not found [message #426787 is a reply to message #251968] Mon, 19 October 2009 11:22 Go to previous messageGo to next message
rosma
Messages: 2
Registered: October 2009
Location: selangor
Junior Member

i got this problem(ORA-02291: integrity constraint (SMP.OL_PERS_ID_ADDR_REFER) violated - parent key not found)...anyone can help me....plz..i dont know what to do coz i'm new in programming..

this is a sql for insert----->>>>>

<!------start----->

String insertAddressInt = "INSERT INTO ol_adm_person_address_temp " +
" (padd_id, "+
" addt_code_addtype, " +
" coun_id_country, " +
" pers_id, " +
" padd_addr1, " +
" padd_postcode, " +
" padd_addr2, " +
" padd_city, " +
" padd_tel, "+
" padd_fax, " +
" padd_createddate, " +
" padd_createdby, " +
" padd_email, " +
" padd_negeri, "+
" padd_addr3, " +
" padd_hp_no, "+
" padd_iscorresadd " +

" ) " +
"VALUES (adm_person_address_SEQ.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?,?,sysdate,?, ?, ?, ?, ?, ?) " ;



String insertAddressObjInt[][] = {

{"str","CURRENT"},
{"int",cor_country},
{"int",persid},
{"str",cor_add1},
{"str",cor_add2},
{"str",cor_poscode},
{"str",cor_city},
{"str",cor_tel},
{"str",cor_fax},
{"int",persid},
{"str",cor_email},
{"str",cor_state},
{"str",cor_add3},
{"str",cor_hp},
{"int",is_correspondence}

};


<!-----------end----------------->






Re: ORA-02291: integrity constraint violated - parent key not found [message #426789 is a reply to message #426787] Mon, 19 October 2009 11:35 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
This error means you are trying to INSERT a row into a child table where no corresponding parent row exists.
Re: ORA-02291: integrity constraint violated - parent key not found [message #426794 is a reply to message #426787] Mon, 19 October 2009 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-02291: integrity constraint (%s.%s) violated - parent key not found
 *Cause: A foreign key value has no matching primary key value.
 *Action: Delete the foreign key or add a matching primary key.

Regards
Michem
Re: ORA-02291: integrity constraint violated - parent key not found [message #427091 is a reply to message #251968] Wed, 21 October 2009 01:37 Go to previous messageGo to next message
rosma
Messages: 2
Registered: October 2009
Location: selangor
Junior Member

ok..thanks a lot...
i'm disable constraint in database..
Re: ORA-02291: integrity constraint violated - parent key not found [message #427098 is a reply to message #427091] Wed, 21 October 2009 02:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rosma wrote on Wed, 21 October 2009 08:37
ok..thanks a lot...
i'm disable constraint in database..

You think that is the solution?
Why not simply drop the constraint instead? Constraints on the database are useless anyway, since your application will no doubt make sure that no invalid data gets inserted, right?





If you answered "yes" to any of the above, I would suggest you start reading some documentation on data-integrity
Re: ORA-02291: integrity constraint violated - parent key not found [message #427101 is a reply to message #427098] Wed, 21 October 2009 02:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, he just has found great short-term job security.

Instead of a few minutes (maybe hours) trying to figure out the correct way to insert he will have months and months of fun hunting down logical data errors. Laughing
Re: ORA-02291: integrity constraint violated - parent key not found [message #427107 is a reply to message #427101] Wed, 21 October 2009 02:59 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Smile
But, looking from the bright side, he will have plenty of time when the powers that be find out what he did...
Previous Topic: procedure to change password
Next Topic: Same query returns differenent result sets in oracle 9i and 10g
Goto Forum:
  


Current Time: Sun Dec 04 02:55:01 CST 2016

Total time taken to generate the page: 0.06564 seconds