Re: Foreign key referential actions

On Tue, 13 Nov 2001, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > Right now, referential actions get deferred along with normal
> > checks and run against the state of the database at that time.
> > I think this violates SQL92 11.8 General Rules 4-6 and have some
> > reasoning and proposed ideas towards making it more complient
> > although I don't actually have an implementation in mind for
> > the most correct version. :(
>
> I'm not convinced. 11.8 GR 1 refers to clause 10.6 as specifying
> when the referential constraint is to be checked. 10.6 says that
> immediate-mode constraints are checked "on completion" of each SQL
> statement. (It doesn't say anything about deferred-mode constraints,
> but I suppose those are checked at end of transaction.)
> I think the intended meaning is that the actions caused by the
> constraint are taken when the constraint is checked, which is either
> end of statement or end of transaction. Which is what we're doing
> now.

But checking the constraint and the actions are not necessarily the
same thing, I believe they're meant as two components. There's a
constraint which says what is a legal state of the database and
there are actions which make modifications to the state of the
database based on the deletes and updates.

For example, in GR 5, it uses the present tense. "and a row
of the referenced table that has not previously marked for
deletion *is* marked for deletion..." (emph. mine). I'd
read that to mean that the following occurs at the time. If
they wanted it to be at the constraint check time, that should
be "has been" or "was" because other places it says things about
how rows that are marked for deletion are effectively deleted
prior to the checking of any integrity constraint (13.7 GR 4
for example) so there'd be no rows remaining that were marked
for deletion at that point. I guess I'm just reading it with a
different set of semantic filters for the language.

Behaviorally I would think that a sequence like:
begin;
insert into pk
insert into fk
delete from pk
insert into pk
insert into fk
end;
would leave you with one row in each, rather than a row in pk
and none in fk or one in pk and two in fk.