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