Re: Near-duplicate RI NO ACTION and RESTRICT triggers

On 19 June 2012 17:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think that the argument for having the RESTRICT triggers behave
> like this is that the SQL spec envisions the RESTRICT check occurring
> immediately when the individual PK row is updated/deleted, and so there
> would be no opportunity for another PK row to be updated into its place.
> (Or, in plainer English, RESTRICT should mean "you can't modify this
> row's keys at all if it has dependents".)  Because we implement RESTRICT
> through an AFTER trigger that can't run earlier than end-of-statement,
> we can't exactly match the spec's semantics, but we can get fairly
> close so long as you don't think about what would be seen by
> e.g. user-written triggers executing during the statement.
>
> I'm happy with continuing to have this behavioral difference between
> the two sets of triggers, but wanted to throw it up for discussion:
> does anyone think it'd be better to apply ri_Check_Pk_Match in the
> RESTRICT triggers too?
>

In SQL:2008 they've re-worded the descriptions of these actions and
added an explicit note to clarify the intended difference:

"""
— ON UPDATE RESTRICT: any change to a referenced column in the
referenced table is prohibited if there
is a matching row.
— ON UPDATE NO ACTION (the default): there is no referential update
action; the referential constraint
only specifies a constraint check.

NOTE 38 — Even if constraint checking is not deferred, ON UPDATE
RESTRICT is a stricter condition than ON UPDATE NO
ACTION. ON UPDATE RESTRICT prohibits an update to a particular row if
there are any matching rows; ON UPDATE NO
ACTION does not perform its constraint check until the entire set of
rows to be updated has been processed.
"""

and there's a similar note in the DELETE case. So I think the current
behaviour is correct, and there are probably genuine use cases for
both types of check.

Regards,
Dean