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