Bug or feature

Поиск
Список
Период
Сортировка
От Kyle
Тема Bug or feature
Дата
Msg-id 3A19770D.AFAAECEF@actarg.com
обсуждение исходный текст
Ответы Re: Bug or feature  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список 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;
 
 

Вложения

В списке pgsql-sql по дате отправления:

Предыдущее
От: Kovacs Zoltan Sandor
Дата:
Сообщение: Re: pgpl-problem, what's wrong with my loop?
Следующее
От: M.Feldtmann@t-online.de (Marten Feldtmann)
Дата:
Сообщение: Re: Using a postgres table to maintain unique id?