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

BUG #3933: Update problem for 3 Foreign Keys referencing 1 field in another table (I'm not newbie in using SQL)

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 (view raw or flat)
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

Responses

pgsql-bugs by date

Next:From: Heikki LinnakangasDate: 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 HaganderDate: 2008-02-05 17:56:39
Subject: Re: [BUGS] BUG #3909: src\tools\msvc\clean.bat clears parse.h file

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