Re: [HACKERS] 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(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.