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. :-(
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 |