Link to home
Start Free TrialLog in
Avatar of cookiejar
cookiejarFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cookiejar

ASKER

Even though, I have not done a commit for Table A?
Avatar of Sean Stuber
Sean Stuber

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