Bug or feature

From: Kyle <kyle(at)actarg(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Bug or feature
Date: 2000-11-20 19:10:05
Message-ID: 3A19770D.AFAAECEF@actarg.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Here's an interesting test of referential integrity. I'm not sure if
this is working the way it should or if it is a bug.

I'm trying to update the primary key in records that are linked together
from the two different tables. My initial assumption was that because
of the cascade, I could update the primary key only in the gl_hdr table
and it would cascade to the gl_items table. I have two separate updates
of gl_items shown below. One updates the key in gl_items explicitly,
the other tries to wait and allow the cascade to do it. Only the first
one works (try commenting one in/out at a time).

Unless I update the glid explicitly in gl_items, I get an RI violation
when it tries to update the gl_hdr record.

--Test RI in the general ledger

drop table gl_hdr;
drop table gl_items;

create table gl_hdr (
glid int4,
hstat varchar(1),
constraint gl_hdr_pk_glid primary key (glid)
);

create table gl_items (
glid int4,
inum int4,
istat varchar(1),
primary key (glid, inum),

constraint gl_items_fk_glid
foreign key (glid) references gl_hdr
on update cascade
deferrable initially deferred
);

insert into gl_hdr (glid,hstat) values (1,'w');
insert into gl_items (glid,inum,istat) values (1,1,'w');
insert into gl_items (glid,inum,istat) values (1,2,'w');

select * from gl_hdr h, gl_items i where h.glid = i.glid;

begin;

--This one works:
-- update gl_items set glid = 1000, istat = 'c' where glid = 1;

--This one doesn't:
update gl_items set istat = 'c' where glid = 1;

update gl_hdr set glid = 1000, hstat = 'c' where glid = 1;
end;

select * from gl_hdr h, gl_items i where h.glid = i.glid;

Attachment Content-Type Size
kyle.vcf text/x-vcard 185 bytes

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Max Fonin 2000-11-20 22:01:33 MySQL -> Postgres dump converter
Previous Message Bruno Boettcher 2000-11-20 17:47:00 Re: pgpl-problem, what's wrong with my loop?