Re: Referential integrity

On Fri, 26 Oct 2001, Mihai Gheorghiu wrote:

> I have a table named T1 that has two signature fields, F1 and F2.
> Both have referential integrity defined as references users table on update
> cascade on delete set null.
> When I try to change a certain username in users table, I get an error
> message like Referential integrity violation key referenced from T1 not
> found in users.
> My guess is that this happens if a record in T1 has both F1 and F2 with the
> value I'm trying to change. Probably the two referential integrity triggers
> act completely separately, e.g. when trigger for F1 updates F1, the record
> it is trying to save still has F2 with the old value, which violates
> referential integrity.
> Am I correct? How can I get out of this situation and still get the
> referential integrity I need.

You are probably correct.  The constraint sometimes still notices the
intermediate states that are not complient and reports them as errors.
I don't know of a real workaround, although I believe I posted an
early test version of a patch that helps prevent these cases a while
ago, you may be able to find it in the archives.  If not, I think
I still have it around (it's not the appropriate final patch which
is why it's not in there, but it's a reasonable temporary one I think,
unfortunately mostly untested in real systems)