Use of inserted data that has not been committed

I have multiple inserts and will not commit until all inserts are successful.

One of the table's values depends upon data in one of the table' s insert that has not been committed..

Is that data available in the table to use?  
For example I need to use one of the table's primary key as a foreign key  in another table whose data is being inserted.

Insert into table a value(primary key, name

I need to insert tablea primary key into tableb as a foreign key before table a insert has been committed.
Insert into table  b (tablea.primary key, nameb

commit
cookiejarAsked:
Who is Participating?
 
sdstuberCommented:
as long as you've done the insert to A inside the same session as the insert to B  the the A/B relation can be evaluated correctly.

easiest method of evaluation here is to simply try it
0
 
cookiejarAuthor Commented:
Even though, I have not done a commit for Table A?
0
 
sdstuberCommented:
yes - but you don't have to trust me.

Just test it, it's easy to see for yourself

SQL> CREATE TABLE tablea
  2  (
  3      primary_key   NUMBER PRIMARY KEY,
  4      name          VARCHAR2(50)
  5  );

Table created.

SQL> CREATE TABLE tableb
  2  (
  3      child_key    NUMBER PRIMARY KEY,
  4      parent_key   NUMBER     REFERENCES tablea(primary_key),
  5      name         VARCHAR2(50)
  6  );

Table created.

SQL> INSERT INTO tablea
  2       VALUES (1, 'test parent');

1 row created.

SQL> REM Note I have not committed anything yet
SQL> INSERT INTO tableb
  2       VALUES (1, 1, 'test child - succeeds');

1 row created.

SQL> REM The child of uncommitted parent-1 succeeded
SQL> REM
SQL>
SQL> REM But the next one will fail because it has no parent-2
SQL> INSERT INTO tableb
  2       VALUES (2, 2, 'test child - fails');
INSERT INTO tableb
*
ERROR at line 1:
ORA-02291: integrity constraint (SDS.SYS_C0070303) violated - parent key not
found

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.