We got a little dispute in the FKEY project :-)

    In  section 11.9, the SQL3 draft explicitly discribes what to
    do for referential actions ON DELETE  and  ON  UPDATE.  First
    there seems to be an incompatibility between SQL3 and SQL-92.
    While Date describes and Oracle implements NO ACTION to raise
    an  exception  if  a  PK delete leaves an unsatisfied foreign
    key, the SQL3 specs explicitly define that behaviour for  the
    RESTRICT action.

    Second,  there's absolutely nothing said about anything to do
    for NO ACTION in SQL3. Thus,  our  current  implementaion  in
    fact  doesn't  do  anything meaningful. That makes it totally
    legal, to delete a  PK  leaving  an  unsatisfied  FK  behind,
    resulting  in  an  in  fact  violation.  And NO ACTION is the
    default if no referential actions  given  explicitly  in  the
    schema.

    Don  Baccus  now  suggested, to interpret NO ACTION as "if it
    would result in a  violation,  then  silently  rollback  this
    update  for  the PK row in question".  Not to speak about the
    technical problems arising from an attempt to do so,  but  as
    said,  such  a  behaviour  is  nowhere  mentioned in the SQL3
    draft.  OTOH it would close the possible  violation  hole  in
    our implementation of FOREIGN KEY.

    What  do others think about it? We need a decision urgent, or
    going for the suppress/rollback will cause a  release  delay,
    definitely.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Sat, 5 Feb 2000 21:04:16 +0100 (CET)
wieck@debis.com (Jan Wieck)
FOREIGN KEY !!!!!
At 09:04 PM 2/5/00 +0100, Jan Wieck wrote:
>We got a little dispute in the FKEY project :-)

Etc...Jan and I have crossed a couple of e-mails.

After he and I tossed our thoughts back-and-forth it appeared
to both of us that SQL3 seemed to be defining "NO ACTION"
differently than in SQL92.

Then I remembered that Date's SQL92 primer has an appendix
on SQL3.   I could've saved us all a bunch of trouble if I
remembered earlier...

By the time you and I read this, Jan and I might well be in
"what exactly should we implement now that we know how it is
SUPPOSED to work" mode, rather than "how is it supposed to
work?" mode.

For those into self-flagellation and other forms of self-inflicted
pain, spend an hour or so with the SQL3 standard trying to figure
out how "NO ACTION" is supposed to work and how it differs from
"RESTRICT" before cheating and reading this excerpt from Date.

Here's my note to Jan that he didn't quite have a chance to read
before posting to the hacker's list:

"OK, mystery solved, I remembered that Date has an appendix on SQL3.
Fortunately, he has a short section on "RESTRICT" vs. "NO ACTION".

We're all wrong :)

>From his SQL3 appendix...

F.4 INTEGRITY

Referential Action RESTRICT

In addition to ... CASCADE, SET NULL [etc] ... SQL3 supports
a new [referential action] RESTRICT.  RESTRICT is very similar - but
not quite identical - to NO ACTION.  The subtle difference between
them is as follows.  Note: to fix our ideas, we concentrate here
on the delete rule; the implications for the update rule are
essentially similar.

o Let T1 and T2 be the referenced table and the referencing
  table, respectively; let R1 be a row of T1, let R2 be a row
  of T2 that corresponds to row R1 under the referential 
  constraint in question.  What happens if an attempt is made
  to delete row R1?

o Under NO ACTION [equiv. to SQL92] the system - conceptually,
  at least - actually performs the requested DELETE, discovers
  row R2 now violates the constraint, and so undoes the DELETE.

o Under RESTRICT, by contrast, the system realizes "ahead of
  time" that row R2 exists and will violate the constraint if
  R1 is deleted, and so rejects the DELETE out of hand.
"

The standard also mentions (I've dug around a bit) that RESTRICT
raises a "restrict violation" exception.  The "NO ACTION" case
conceptually might raise an "integrity constraint violation"
instead, and perhaps to be compliant MUST raise that constraint.




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Sat, 05 Feb 2000 12:27:38 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] FOREIGN KEY !!!!!
> o Under RESTRICT, by contrast, the system realizes "ahead of
>   time" that row R2 exists and will violate the constraint if
>   R1 is deleted, and so rejects the DELETE out of hand.

    That'd mean in last consequence, that RESTRICT actions aren't
    DEFERRABLE, while the rest of their constraint definition is!
    Anyway,  cannot  work  with  the actual implementation of the
    trigger queue, so we could either make RESTRICT and NO ACTION
    identical (except for different ERROR messages), or leave the
    SQL3 RESTRICT out of 7.0 while changing NO ACTION to fire the
    message.

    I'd  prefer  to have them identical in 7.0, because according
    to Date they have no semantic difference,  so  it'll  buy  us
    little  if we complicate the trigger stuff more than required
    right now.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Sat, 5 Feb 2000 21:30:04 +0100 (CET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] FOREIGN KEY !!!!!
At 09:30 PM 2/5/00 +0100, Jan Wieck wrote:
>> o Under RESTRICT, by contrast, the system realizes "ahead of
>>   time" that row R2 exists and will violate the constraint if
>>   R1 is deleted, and so rejects the DELETE out of hand.

>    That'd mean in last consequence, that RESTRICT actions aren't
>    DEFERRABLE, while the rest of their constraint definition is!

That's how I read it, too.  Pardon me while I run off to vomit in
the toilet.

>    Anyway,  cannot  work  with  the actual implementation of the
>    trigger queue, so we could either make RESTRICT and NO ACTION
>    identical (except for different ERROR messages), or leave the
>    SQL3 RESTRICT out of 7.0 while changing NO ACTION to fire the
>    message.

>    I'd  prefer  to have them identical in 7.0, because according
>    to Date they have no semantic difference,  so  it'll  buy  us
>    little  if we complicate the trigger stuff more than required
>    right now.

If others on the list agree, I think this is an excellent idea.  I
see no semantic difference that the application will see, either,
other than a difference in execution time.

Raising the exception before the delete or update seems more an efficiency
hack than anything, i.e. it's much less expensive to short-circuit the
delete/update rather than finish it, check afterwards, and roll it
back.




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Sat, 05 Feb 2000 12:47:42 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] FOREIGN KEY !!!!!