Home » SQL & PL/SQL » SQL & PL/SQL » Inserting record in Parent & child table
Inserting record in Parent & child table [message #427550] Fri, 23 October 2009 04:05 Go to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Using Toad 9.0.1.8

I have two tables,one is PArent table and an another one is Child table.

I need to INSERT a new record into the Parent table and then immediately INSERT a corresponding record into the Child table.

When I am trying inserting,I m getting "Integrity Constraint,Parent Key not Found" problem

Pls help me

thanks in adavance
Re: Inserting record in Parent & child table [message #427551 is a reply to message #427550] Fri, 23 October 2009 04:13 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Copy & Paste your sqlplus session for us to see the entire steps that you did.


XE@SQL> create table t_parent(x int primary key);

Table created.

XE@SQL> create table t_child(a int primary key,b references t_parent(x));

Table created.

XE@SQL> insert into t_parent values (1);

1 row created.

XE@SQL> insert into t_child values (1,1);

1 row created.

XE@SQL> insert into t_child values (2,2);
insert into t_child values (2,2)
*
ERROR at line 1:
ORA-02291: integrity constraint (test.SYS_C004211)
violated - parent key not found


XE@SQL>





[Updated on: Fri, 23 October 2009 04:15]

Report message to a moderator

Re: Inserting record in Parent & child table [message #427552 is a reply to message #427550] Fri, 23 October 2009 04:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That strongly suggests that the value you've got in the Foreign key column doesn't exist in the parent table.

You could try showing us some code - that helps sometimes.....
Re: Inserting record in Parent & child table [message #427555 is a reply to message #427550] Fri, 23 October 2009 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you insert FIRST in the parent table and THEN in the child table as you say it is not possible you get this error (assuming you do it in the same session).

Regards
Michel
Re: Inserting record in Parent & child table [message #427558 is a reply to message #427552] Fri, 23 October 2009 04:40 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
declare
cursor c14 is
select p.id,p.unit,p.group,p.loc,p.date from
parameter p,model m
where p.id = m.id and p.unit = m.unit and p.loc not in
(select loc from prof where model = 'FANNA');
begin
for x in c14 loop
insert into prof(id,unit,loc,date,refer) values
(id_seq.nextval,x.unit,x.loc,,x.date,'AUTO');

insert into map(id,unit,loc) values
(id_seq.nextval,x.unit,x.loc);
commit;
end loop;
end;


here, Prof is the parent and MAP is the child table
id,unit,loc are the primary fields in Parent
id,unit,loc are the primary fields in tabel

pls find my code above


Re: Inserting record in Parent & child table [message #427559 is a reply to message #427555] Fri, 23 October 2009 04:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Michel - you have a touching faith in the competence of the average developer.

My bet is that they're using sequences, and are using NEXTVAL instead of CURRVAL in the second insert.

[Drat - the OP replied before I'd posted.]

[Updated on: Fri, 23 October 2009 04:46]

Report message to a moderator

Re: Inserting record in Parent & child table [message #427560 is a reply to message #427559] Fri, 23 October 2009 04:45 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
@Jrowbottom

You are amazing..I did the same mistake.thanks
Re: Inserting record in Parent & child table [message #427563 is a reply to message #427559] Fri, 23 October 2009 04:47 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
./fa/4568/0/ Touché!

Regards
Michel
Previous Topic: Help needed in populating a column with the column value of another row
Next Topic: to get rowcount of dynamically executed query
Goto Forum:
  


Current Time: Fri Dec 09 06:18:49 CST 2016

Total time taken to generate the page: 0.12525 seconds