Re: [GENERAL] Foreign Key

Does cascade delete work for you if there are more than one tables referencing
the primary key, because it throws up an error for me when I delete an entry in
the primary key table.

-- Anand

Howie wrote:

> On Wed, 6 Oct 1999, Mike Mascari wrote:
>
> > [SNIP]
> >
> > Now do another update (in the same session):
> >
> > update employee set emp_id=6;
> >
> > Followed by a select:
> >
> > select * from emp_expense;
> >
> > What's the emp_id value?
>
> doesnt even get that far:
>
> caffeine=> update employee set emp_id=6;
> UPDATE 1
> (emp_expense updated accordingly)
> caffeine=> update employee set emp_id=3;
> ERROR:  expense_empid_fk: tuple references non-existing key in employee
> (emp_expense not updated; fki breaks, whole countries are washed away)
>
> > In the version of refint.c which was release with
> > 6.5.0, there was an error because after the first
> > cascading update trigger was executed, the plan was
> > saved (which includes the value of the foreign key
> > to be updated), since the cascading update code was
> > simply the cascading delete code. Saving the SPI
> > plan for deletes is fine, but for updates it can
> > cause either (a) the wrong value to updated or
> > (b) an insertion of new rows.
> >
> > Perhaps this has been fixed, but I doubt it.
>
> i havent had a chance to grab 6.5.2 ( latest, iirc ) yet and test this
> out.  actually, i suppose that after check_foreign_key()
> completes it should discard its plan, yesno ?  if so, ( and i havent done
> any SPI stuff just yet ), the 'fix' would be to insert the proper function
> call inside of check_foreign_key's "* Ok, execute prepared plan(s)." loop.
>
> a quick lookie at programmer/spi-spisaveplan.htm ( grin ) doesnt show any
> SPI_forgetPlan() functions, though.  and SPI_saveplan() "saves a passed
> plan (prepared by SPI_prepare) in memory protected from freeing by
> SPI_finish...", which is bad in this situation.
>
> so the fix is to wait for 6.6, which hopefully wont have this problem
> since refint's functionality should be inside of the backend proper, or
> dont do two cascading updates at the same time, beating users on the head
> with large books and/or unused machines if they do so.
>
> ---
> Howie <caffeine@toodarkpark.org>   URL: http://www.toodarkpark.org
> "Just think how much deeper the ocean would be if sponges didn't live there."
>
> ************