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

Referential integrity problem

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Referential integrity problem
Date: 2001-06-18 16:48:57
Message-ID: 200106181648.f5IGmve54550@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Denis Sbragion (d(dot)sbragion(at)infotecna(dot)it) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Referential integrity problem

Long Description
It looks like performing operation in two steps on tables with multiple deferred referential integrity constraints between them is not allowed. Not sure this is a bug, may be it is required by the standards, but sure it is rater counterintuitive (may be, of course, I'm completely wrong). Most of the times this is just annoying, sometimes it doesn't let you do updates, i.e. whenever you cannot perform the update in a single step (I found at least one case). Tested both on 7.0.3 and 7.1.2, with same results.

P.S. Great job guys. 7.0.3 was very good, but 7.1.2 is simply wonderful.

Sample Code
Here it is an oversimplyfied example:

create table tablea
 (
  fielda integer,
  fieldb varchar(128),
  constraint tablea_pkey primary key (fielda)
 );

create table tableb
 (
  fielda integer,
  fieldb integer,
  fieldc varchar(128),
  fieldd integer,
  constraint tableb_pkey primary key (fielda, fieldb),
  constraint tablea_to_tableb foreign key (fielda) references
   tablea (fielda) on delete restrict on update restrict
   deferrable initially deferred,
  constraint scndtablea_to_tableb foreign key (fieldd) references
   tablea (fielda) on delete restrict on update restrict
   deferrable initially deferred
 );

insert into tablea (fielda, fieldb) values (1, 'Key 1');
insert into tableb (fielda, fieldb, fieldc, fieldd) values (1, 1, 'Ref to Key 1', 1);

Performing:

begin;
update tableb set fielda = 2, fieldd = 2 where fielda = 1;
update tablea set fielda = 2 where fielda = 1;
commit;

it's ok as it should. If you do the same thing above (now reversed, of course) in two steps, i.e:

begin;
update tableb set fielda = 1 where fielda = 2;
update tableb set fieldd = 1 where fieldd = 2;
update tablea set fielda = 1 where fielda = 2;
commit;

gives:

ERROR:  scndtablea_to_tableb referential integrity violation - key referenced from tableb not found in tablea

I hope it's clear.

No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: Patti MorganDate: 2001-06-18 17:26:12
Subject: RE: Referential integrity problem
Previous:From: Karel ZakDate: 2001-06-18 10:34:13
Subject: Re: to_date() month translation

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