Questions on RI spec (poss. bugs)
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.
My understanding of the spec is that that sequence should
have succeeded, but I could very well be wrong. Changing the
noaction check to fix this is probably fairly minimal (making
sure that there isn't now a key with the old value before checking
for violated rows would probably be sufficient for match full and
unspecified). And I guess technically this could happen for
immediate constraints as well if a single update changed a key to
a new value and another to the old one so the constraint was still
satisifed.
But, this brings up a question for the referential actions.
It doesn't look like the actions are limited to whether or not the
row would be violating, but instead based on what row it was associated
with before. (Makes sense, you'd want a cascade update to keep
the same associations). But that made me wonder about exactly
*when* the actions were supposed to take place for deferred constraints.
You could say at check time, but that doesn't make sense for RESTRICT
really, and restrict doesn't have any special wording I see in its
definition. So if you had a deferred on delete cascade constraint, and you
do begin; delete from pk; select * from fk; end; do you see the fk rows
that were associated with the deleted pk rows?
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.
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?
If I'm right that it should be a "triggered data change
violation", the problem is just changing into one we have
with delete/reinsert in the ON DELETE CASCADE case. Haven't
tested, but the current implementation shouldn't detect it.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Jan Wieck writes:
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"?
Triggered data change violations can only occur if the same attribute is
changed twice during the same *statement*, not transaction.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Peter Eisentraut wrote:
Jan Wieck writes:
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"?Triggered data change violations can only occur if the same attribute is
changed twice during the same *statement*, not transaction.
Do we also get "Triggered data change violations" when we delete and
then
insert on the FK side in a single transaction ?
I just had to remove a FK constraint because I could not figure ot where
the violation was coming from ;(
-----------------
Hannu
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).