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 |
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? |