Thread: Two foreign keys in one table both referencing same record in primary table gives error on update of primary table

Bob Soeters (bob@iway.nl) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Two foreign keys in one table both referencing same record in primary table gives error on update of primary table

Long Description
(PostgreSQL 7.1.1 on i686-pc-linux-gnu, compiled by GCC 2.95.3)

Say you have two tables, test1 and test2.

Test1 is the primary table and contains only one integer field, which also is the primary key for that table.

Test2 has two integers in it, both of which are referencing the primary key of test1.

On update or delete of the primary key in table test1, records in table test2 are supposed to be updated or deleted
accordingly.

Consider the example code for the setup.

Now, if someone updates a record in test1, say, we want to update the record with id = 1 in it, so that id becomes 6,
andthere's a record in the second table test2 for which _both_ id's are referencing 1, the update won't be completed.
Instead,you'll get an error stating that there's a referential integrity error. 

Deleting of such records through referencing foreign keys is no problem at all tho, that gets completed as expected.

Thanks in advance,
and keep up the good work,
I enjoy PostgreSQL daily ;)
Regards, Bob Soeters

Sample Code
drop table test2;
drop table test1;

create table test1 (

id integer not null,

primary key (id)
);

create table test2 (

id1 integer   not null,
id2 integer   not null,

foreign key (id1)
references test1 (id)
on update cascade
on delete cascade,

foreign key (id2)
references test1 (id)
on update cascade
on delete cascade
);

insert into test1 values (1);
insert into test1 values (2);
insert into test1 values (3);

insert into test2 (id1, id2) values (1, 2);
insert into test2 (id1, id2) values (1, 3);
insert into test2 (id1, id2) values (2, 1);
insert into test2 (id1, id2) values (3, 1);

update test1 set id = 6 where id = 1;

-- everything's ok here, no errors, things get updated ok


-- -- BUT -- --


drop table test2;
drop table test1;

create table test1 (

id integer not null,

primary key (id)
);

create table test2 (

id1 integer   not null,
id2 integer   not null,

foreign key (id1)
references test1 (id)
on update cascade
on delete cascade,

foreign key (id2)
references test1 (id)
on update cascade
on delete cascade
);

insert into test1 values (1);
insert into test1 values (2);
insert into test1 values (3);

insert into test2 (id1, id2) values (1, 1); -- <<<

insert into test2 (id1, id2) values (1, 2);
insert into test2 (id1, id2) values (1, 3);
insert into test2 (id1, id2) values (2, 1);
insert into test2 (id1, id2) values (3, 1);

update test1 set id = 6 where id = 1;

-- produces
-- ERROR:  <unnamed> referential integrity violation - key referenced
-- from test2 not found in test1

-- whereas

delete from test1 where id = 1;

-- will succeed and leave less records in test2 as well,
-- completely conform to the referencing foreign keys'
-- on delete statements defined with table test2


No file was uploaded with this report

Yes, it's because it sees the intermediate
state (6,1) when it shouldn't (it processes
each foreign key separately and does the cascade
update which places it in an "invalid" state
since the 1 key no longer exists).

I believe the fixes I've been working on for deferred
constraints will make this case work as well.

On Wed, 18 Jul 2001 pgsql-bugs@postgresql.org wrote:

> Bob Soeters (bob@iway.nl) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description Two foreign keys in one table both referencing same
> record in primary table gives error on update of primary table

Thank you Stephan, for the quick reply.

Please, do you know if your fixes will be available soon,
or are they already in the development release or any of the production
releases ?

> Yes, it's because it sees the intermediate
> state (6,1) when it shouldn't (it processes
> each foreign key separately and does the cascade
> update which places it in an "invalid" state
> since the 1 key no longer exists).
>
> I believe the fixes I've been working on for deferred
> constraints will make this case work as well.
>
> > Bob Soeters (bob@iway.nl) reports a bug with a severity of 2
> > Short Description Two foreign keys in one table both referencing same
> > record in primary table gives error on update of primary table