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;
begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;;;;;; version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard
