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

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

pgsql-sql by date

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

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