Re: Questions on RI spec (poss. bugs)

On Tue, 21 Nov 2000, Jan Wieck wrote:

> Stephan Szabo wrote:
> >
> > There's a message on -general about a possible
> > problem in the deferred RI constraints. He was doing a
> > sequence like:
> > begin
> > delete
> > insert
> > end
> > and having it fail even though the deleted key was back in
> > place at the end.
>
> Isn't that (delete and reinsert the same PK) what the
> standard means with "triggered data change violation"?
>
> It is a second touching of a unique matching PK. And in this
> case the standard doesn't define a behaviour, instead it says
> you cannot do so.

As Peter said, it really looks like the 99 draft anyway means twice in a
single statement not transaction which is probably there to prevent
infinite loops.

> In the case of reinserting a deleted PK, does the new PK row
> inherit the references to the old PK row? If so, an ON DELETE
> CASCADE must be suppressed - no?
I'm not sure because it's unclear to me whether ri actions are actually
deferred. Restrict for example sounds like it occurs immediately on the
statement and it's not worded differently from others in the draft I have.
So, it's possible that the actions are supposed to occur immediately on
the statement, even if the constraint check is deferred. I really don't
know, but it would explain a behavioral difference between restrict and
noaction that makes having both make sense (restrict prevents you from
moving away - no action lets you move away as long as the constraint is
okay at check time).