From: | "irfin" <irfin(at)latifolia(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #3933: Update problem for 3 Foreign Keys referencing 1 field in another table (I'm not newbie in using SQL) |
Date: | 2008-02-05 18:41:31 |
Message-ID: | 200802051841.m15IfVIn084761@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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)
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
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2008-02-05 19:57:05 | Re: BUG #3933: Update problem for 3 Foreign Keys referencing 1 field in another table (I'm not newbie in using SQL) |
Previous Message | Magnus Hagander | 2008-02-05 17:56:39 | Re: [BUGS] BUG #3909: src\tools\msvc\clean.bat clears parse.h file |