Received: from actarg.com (host25.actarg.com [209.180.91.25] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eAKJC0s35655 for ; Mon, 20 Nov 2000 14:12:00 -0500 (EST) (envelope-from kyle@actarg.com) Received: from chi.actarg.com (IDENT:root@chi.actarg.com [192.168.2.16]) by actarg.com (8.9.3/8.9.3) with ESMTP id MAA25372 for ; Mon, 20 Nov 2000 12:10:03 -0700 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id MAA08265 for ; Mon, 20 Nov 2000 12:10:05 -0700 Message-ID: <3A19770D.AFAAECEF@actarg.com> Date: Mon, 20 Nov 2000 12:10:05 -0700 From: Kyle Organization: Action Target Inc. X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Bug or feature Content-Type: multipart/mixed; boundary="------------A81608BD76075F196BED61DD" X-Archive-Number: 200011/162 X-Sequence-Number: 193 This is a multi-part message in MIME format. --------------A81608BD76075F196BED61DD Content-Type: multipart/alternative; boundary="------------87DAA7BFC3D30F5D9D927176" --------------87DAA7BFC3D30F5D9D927176 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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; --------------87DAA7BFC3D30F5D9D927176 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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;
 
  --------------87DAA7BFC3D30F5D9D927176-- --------------A81608BD76075F196BED61DD Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Content-Disposition: attachment; filename="kyle.vcf" 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 --------------A81608BD76075F196BED61DD--