There are three tables:

create table C(id_row integer primary key)

create TABLE A(col_a1,col_a2,col_a3,..., id_row integer primary key, constraint fk_a_c foreign key (id_row)  references C(id_row))

create TABLE B(col_b1,col_b2,col_b3,..., id_row integer primary key, constraint fk_b_c foreign key (id_row)  references C(id_row))

Table C is populated with a number of values.
I have to write an application that will allow its users to insert in table A or B an id_row already present in table C, constrained by the fact that a single id_row can be present at most in only one of A and B.

The application I have to write will establish a connection to the database for each user and start a new transaction for every id_row to be inserted. The isolation level must be READ COMMITTED.

The procedure I would write to implement mutually exclusive inserts would be this:

 select * from table c where id_row =in_id_row for update; --lock the row in the parent table
 check id_row is not already in a or b, if it is raise exception;
 proceed to insert the row in a or b.

Does anybody know other approaches to achieve this behavior without using 'select for update' or 'lock table'?


One way is to add discriminator column, something like

create table c (id_c int not null, val char(1))
create index idx_c1 on c(id_c,val);
alter table c add constraint chk_val check(val in ('a','b'));

alter table c add constraint c_pk primary key (id_c) using index idx_c1;
alter table c add constraint c_uq unique (id_c,val) using index idx_c1;

create table a(id_a int not null primary key, 
val char(1) default 'a' not null ,
constraint fk_t foreign key(id_a,val)  references c (id_c,val));

alter table a add constraint chk_val_a check (val ='a');

Similar for table (but check constraint should b check(val='b').

Then detail record can be in table a or b, but not in both. Overhead is small thanks to the fact Oracle allows multiple constraint supported by the same index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.