Обсуждение: FOREIGN KEY !!!!!
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 implementsNO ACTION to raise an exception if a PK delete leaves an unsatisfied foreign key, the SQL3 specs explicitlydefine 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 attemptto do so, but as said, such a behaviour is nowhere mentioned in the SQL3 draft. OTOH it would close thepossible 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) #
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 ofT2 that corresponds to row R1 under the referential constraint in question. What happens if an attempt is made to deleterow R1? o Under NO ACTION [equiv. to SQL92] the system - conceptually, at least - actually performs the requested DELETE, discoversrow 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 ifR1 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 Serviceand other goodies at http://donb.photo.net.
> 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 definitionis! Anyway, cannot work with the actual implementation of the trigger queue, so we could either make RESTRICTand NO ACTION identical (except for different ERROR messages), or leave the SQL3 RESTRICT out of 7.0 while changingNO 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) #
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 Serviceand other goodies at http://donb.photo.net.