Обсуждение: Bug or feature

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;
 
 

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