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
