The following bug has been logged online:
Bug reference: 3933
Logged by: irfin
Email address: irfin(at)latifolia(dot)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)
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 (
PRIMARY KEY (id_a)
-- This table has 3 foreign keys that refer to a
-- primary key of the master table
CREATE TABLE BB (
id_a1 integer NOT NULL,
id_a2 integer NOT NULL,
id_a3 integer NOT NULL,
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
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.
pgsql-bugs by date
|Next:||From: Heikki Linnakangas||Date: 2008-02-05 19:57:05|
|Subject: Re: BUG #3933: Update problem for 3 Foreign Keys referencing 1 field in another table (I'm not newbie in using SQL)|
|Previous:||From: Magnus Hagander||Date: 2008-02-05 17:56:39|
|Subject: Re: [BUGS] BUG #3909: src\tools\msvc\clean.bat clears parse.h