insert or update violates foreign key constraint.why?
От | Oleg Mayevskiy |
---|---|
Тема | insert or update violates foreign key constraint.why? |
Дата | |
Msg-id | Pine.LNX.4.58.0405281547380.22455@pandora.hrz.tu-chemnitz.de обсуждение исходный текст |
Ответы |
Re: insert or update violates foreign key constraint.why?
|
Список | 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) REFERENCESpublic.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 ' DECLAREmission RECORD;ret bool; BEGIN ret:=FALSE; raise notice\'begin\';SET CONSTRAINTS ALL IMMEDIATE; FOR mission IN SELECT * FROM public.test1LOOP raise notice\'before update\'; UPDATE public.test2 SET data1=data1+1;ENDLOOP; FOR mission IN SELECT * FROM public.test1LOOP raise notice\'after update\'; DELETE FROM public.test1 WHEREid=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. :-(
В списке pgsql-sql по дате отправления: