• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

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
0
cookiejar
Asked:
cookiejar
  • 2
1 Solution
 
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now