i have 2 tables.
the first table "TAB1", has a primary key "id",
which is the foreign key in the second table "TAB2"
i have 2 stored procedures,
1 stored procedure does
check if there is a row already present in TAB1
with col 2 = $1;
if not present
then
insert into TAB1(col2) values ($1
);
select into "myid" tab1_id from TAB1 where col2 =
$1;
return myid
end if
please note that tab1_id is a serial
bigint.
second stored procedure does
check if there is a row already present in TAB2
with col 2 = $2;
if not present
then
insert into TAB2 (col2, col3) values ($1 ,
$2);
select into "myid" tab2_id from TAB2 where col2 =
$1;
return myid
end if
please note that tab2_id is the seriesl bigint.
also the second argument $2, is the value for the field col3, which is THE
FOREIGN KEY.
in my transaction i do the following
begin
id = call stored procedure 1("abc");
id2 = call stored procedure 2 ("something", id);
commit
however i may have another transaction going in parallel, where i do
this
delete from TAB1 where tab1_id = 5; //5 is just some value
in such a case sometime a foreign key violation occurs when the stored
procedure 2 is called.
Is there any way i can avoid this problem?
thanks,
regards
Surabhi
