2009-08-19

Multi table insert atomic?

Reading SQL and Relational Theory: How to Write Accurate SQL Code. Talking about constraints and data modifications. There should be able to update several tables in one atomic operation. Oracle multi table insert could be something to that direction, but no atomicy. Bug still open from version not any more supported.


SQL> create table a (a_id number(1) primary key
2 , b_id number(1) not null);

Table created.

SQL> create table b (b_id number(1) primary key
2 , a_id not null constraint b_a_fk references a );

Table created.

SQL> alter table a add
2 constraint a_b_fk foreign key (b_id) references b
3 deferrable initially deferred;

Table altered.

SQL> insert all into b values (x,x)
2 into a values (x,x)
3 select 0 x
4 from dual;
insert all into b values (x,x)
*
ERROR at line 1:
ORA-02291: integrity constraint (RAFU.B_A_FK) violated - parent key not found


SQL> insert all into a values (x,x)
2 into b values (x,x)
3 select 1 x
4 from dual;

2 rows created.

SQL> insert into a values (2,2);

1 row created.

SQL> insert into b values (2,2);

1 row created.

SQL> select * from a;

A_ID B_ID
---------- ----------
1 1
2 2

SQL> rollback;

Rollback complete.

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.