insert or update violates foreign key constraint.why?

From: Oleg Mayevskiy <oleg(dot)mayevskiy(at)s2002(dot)tu-chemnitz(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: insert or update violates foreign key constraint.why?
Date: 2004-05-28 13:49:34
Message-ID: Pine.LNX.4.58.0405281547380.22455@pandora.hrz.tu-chemnitz.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

it seems to be a simple problem, but it is not.
i have declared 2 simple tables:

CREATE TABLE public.test1
(
id int4 NOT NULL,
data float4,
CONSTRAINT mytest_pkey PRIMARY KEY (id)
) WITH OIDS;

CREATE TABLE public.test2
(
id1 int4 NOT NULL,
data1 float4,
CONSTRAINT test2_pkey PRIMARY KEY (id1),
CONSTRAINT "$1" FOREIGN KEY (id1) REFERENCES public.test1 (id) ON UPDATE
CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
) WITH OIDS;

then i have written a function in PLPGSQL:

CREATE OR REPLACE FUNCTION public.test_func()
RETURNS bool AS
'
DECLARE

mission RECORD;

ret bool;


BEGIN
ret:=FALSE;
raise notice\'begin\';

SET CONSTRAINTS ALL IMMEDIATE;

FOR mission IN SELECT * FROM public.test1
LOOP
raise notice\'before update\';
UPDATE public.test2 SET data1=data1+1;
END LOOP;

FOR mission IN SELECT * FROM public.test1
LOOP
raise notice\'after update\';
DELETE FROM public.test1 WHERE id=mission.id;
END LOOP;

ret:=TRUE;

raise notice\'end\';

RETURN ret;

END;'
LANGUAGE 'plpgsql' VOLATILE;

my expecting behavior is:
update all rows in test2
delete all from test1 und then delete all from test1 because of the ON
DELETE CASCADE

BUT:

NOTICE: begin
NOTICE: before update
NOTICE: before update
NOTICE: before update
NOTICE: after update
NOTICE: after update
NOTICE: after update
NOTICE: end

ERROR: insert or update on table "test2" violates foreign key constraint
"$1"
DETAIL: Key (id1)=(1) is not present in table "test1".

why?
i have asked already in postgresql chan for help, but nobody could solve
the problem.
I hope you can.

Big THX

Oleg

PS: i tried NOT DEFERRABLE too, it does not work too. :-(

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Atesz 2004-05-28 15:37:20 Re: OR clause causing strange index performance
Previous Message sad 2004-05-28 09:29:35 type regclass casting