Обсуждение: BUG #3933: Update problem for 3 Foreign Keys referencing 1 field in another table (I'm not newbie in using SQL)

The following bug has been logged online:

Bug reference:      3933
Logged by:          irfin
Email address:      irfin@latifolia.com
PostgreSQL version: 8.2.3
Operating system:   Windows XP Professional Service Pack 2 (i586)
Description:        Update problem for 3 Foreign Keys referencing 1 field in
another table (I'm not newbie in using SQL)
Details:

Dear PostgreSQL Developer Team,

Here's the problem, which I think might be a bug: I have three foreign keys
and those keys refer to a primary key of another table. When the primary key
of another table is updated then Postgre raise an error (contraint failed).
But this problem won't happen if I only have two foreign keys.

To describe more precisely, consider this code:

-- This is the master table
CREATE TABLE AA (
 id_a integer,
 content_a char(20),
 PRIMARY KEY (id_a)
);

-- This table has 3 foreign keys that refer to a
-- primary key of the master table
CREATE TABLE BB (
 id_b integer,
 id_a1 integer NOT NULL,
 id_a2 integer NOT NULL,
 id_a3 integer NOT NULL,
 content_b character(20),
 PRIMARY KEY (id_b),
 FOREIGN KEY (id_a1) REFERENCES AA(id_a) ON UPDATE CASCADE,
 FOREIGN KEY (id_a2) REFERENCES AA(id_a) ON UPDATE CASCADE,
 FOREIGN KEY (id_a3) REFERENCES AA(id_a) ON UPDATE CASCADE
);

-- We see that table BB has three foreign keys
-- (which are id_a1, id_a2, and id_a3) and these keys
-- point to the same field in table AA (which is id_a).
-- Now let's continue with this code:

INSERT INTO AA(id_a, content_a)
VALUES (100, 'xyz');

INSERT INTO BB(id_b, id_a1, id_a2, id_a3, content_b)
VALUES (333, 100, 100, 100, 'abc');

-- Now problem araise when I do this:
UPDATE AA SET id_a=222 WHERE id_a=100;


The error message from PostgreSQL is:
ERROR: insert or update on table "bb" violates foreign key constraint
"bb_id_a3_fkey"
SQL state: 23503
Detail: Key (id_a3)=(100) is not present in table "aa".
Context: SQL statement "UPDATE ONLY "public"."bb" SET "id_a2" = $1 WHERE
"id_a2" = $2"

Once again, this error DOESN'T HAPPEN if table BB has only one/two foreign
key(s) instead of three.

Thank you very much for spending your time in reading this message. If this
is really a bug, I hope this information can be useful.


Irfin Afifudin

irfin wrote:
> The following bug has been logged online:
>
> Bug reference:      3933
> Logged by:          irfin
> Email address:      irfin@latifolia.com
> PostgreSQL version: 8.2.3
> Operating system:   Windows XP Professional Service Pack 2 (i586)
> Description:        Update problem for 3 Foreign Keys referencing 1 field in
> another table (I'm not newbie in using SQL)
> Details:
>
> Dear PostgreSQL Developer Team,
>
> Here's the problem, which I think might be a bug: I have three foreign keys
> and those keys refer to a primary key of another table. When the primary key
> of another table is updated then Postgre raise an error (contraint failed).
> But this problem won't happen if I only have two foreign keys.

Please upgrade to the latest 8.2.X minor release, which is 8.2.6 at the
moment (8.3.0 was just announced as well, BTW, you might want to go
straight to that). I believe this was fixed in 8.2.5.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com