Re: Perfornamce Q

On Thu, 23 Jan 2003, Jean-Christian Imbeault wrote:

> Tom Lane wrote:
> >
> > Doesn't matter: any update will fire the foreign-key check trigger.
> >
> > Whether this is necessary or not I dunno, but someone's thought of
> > it before: in the code I see
> > /*
> > * Note: We cannot avoid the check on UPDATE, even if old and new key
> > * are the same. Otherwise, someone could DELETE the PK that consists
> > * of the DEFAULT values, and if there are any references, a ON DELETE
> > * SET DEFAULT action would update the references to exactly these
> > * values but we wouldn't see that weired case (this is the only place
> > * to see it).
> > */
>
> I don't quite understand the comment but I guess it mean that an "ON
> DELETE SET DEFAULT" might be triggered by an UPDATE, and that "ON DELETE
> SET DEFAULT" might affect the PK, AND because of this possible scenario
> the FK check is needed.

The case is if you have ON DELETE SET DEFAULT and you delete the PK row
that is the matching row for the defaults on the FK table (and there are
rows already having that value) it would attempt to update the row to the
default values which would then fire the update/insert trigger which
would see that the row values haven't changed and not check the rows, but
that's wrong because the constraint is violated.