Re: Bug or feature

What is it actually giving you as an error
message in the failing case? Someone pointed
out a problem in deferred constraints recently
and I think this may be related. 

Stephan Szabo
sszabo@bigpanda.com

On Mon, 20 Nov 2000, Kyle wrote:

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