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
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 '