From: Kyle <kyle@actarg.com>
To: pgsql-sql@postgresql.org
Subject: Bug or feature
Date: Mon, 20 Nov 2000 12:10:05 -0700
Message-ID: <3A19770D.AFAAECEF@actarg.com> (raw)
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;
Attachments:kyle.vcf (text/x-vcard, 184B) download
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;;;;;
version:2.1
email;internet:kyle@actarg.com
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kyle@actarg.com
Subject: Re: Bug or feature
In-Reply-To: <3A19770D.AFAAECEF@actarg.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox