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?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: sad
Дата:
Сообщение: type regclass casting
Следующее
От: jarednevans@yahoo.com (Jared Evans)
Дата:
Сообщение: Re: not really SQL but I need info on BLOBs