Skip site navigation (1) Skip section navigation (2)

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

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Two foreign keys in one table both referencing same record in primary table gives error on update of primary table
Date: 2001-07-18 19:21:37
Message-ID: 200107181921.f6IJLba59785@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Bob Soeters (bob(at)iway(dot)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, and there'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


Responses

pgsql-bugs by date

Next:From: Danilo Gonzalez HashimotoDate: 2001-07-18 20:53:06
Subject: Documentation Bug related to Inheritance
Previous:From: Tom LaneDate: 2001-07-18 18:53:22
Subject: Re: libpgtcl doesn't use UTF encoding of TCL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group