Path: utzoo!utgpu!news-server.csri.toronto.edu!bonnie.concordia.ca!thunder.mcrcim.mcgill.edu!snorkelwacker.mit.edu!usc!elroy.jpl.nasa.gov!decwrl!mejac!orchard.la.locus.com!prodnet.la.locus.com!jfr
From: jfr@locus.com (Jon Rosen)
Newsgroups: comp.databases
Subject: Re: how to implement cursor based UPDATE/DELETE
Message-ID: <24490@oolong.la.locus.com>
Date: 17 May 91 00:04:08 GMT
References: <24334@oolong.la.locus.com> <1991May15.171152.557@ingres.Ingres.COM>
Organization: Locus Computing Corp, Los Angeles
Lines: 105

In article <1991May15.171152.557@ingres.Ingres.COM> billc@ingres.com (Bill Coffin, x3387) writes:
>> As for the duplicate row issue, don't you know that relational data
>> bases aren't supposed to have duplicate rows??? :-)
>
>But a cursor can be opened on a query that returns duplicate rows.
> 
 
  Ah, but as I will illustrate, in a proper implementation of the
  relational model, a cursor can NOT be used to update rows if
  duplicates are returned.            

>There are other problems in UPDATE/DELETE through a cursor. 
 
  No sh*t :-)... That's why "updatable cursors" are such a drag.
 
  Let's not forget that updatable cursors are NOT part of the
  relational model but are in fact an arbitrary construct imposed
  on implementations due to the impedance mismatch between a
  set-oriented language like SQL and a record-at-a-time-oriented
  language like COBOL or C.  In addition, (and probably most
  likely), IBM, when implementing SQL, had to pay attention to
  the needs of its most "important" users, i.e., the MIS shops,
  with their oodles of millions of lines of COBOL code with VSAM
  and QSAM data.  Most of these programs do things like loop 
  through a file and update in place those rows that need to
  be updated.  Updatable cursors give you a construct that allows
  you to convert to SQL without as much pain as if you started
  requiring primary key retrieval on every selection.

><stuff deleted>
>You generally can't update/delete through a join, but that's
>a whole 'nother can of worms.  In fact,
>cursor UPDATE/DELETE has all the problems of
>updating through views, and then some more.
 
  Most of this is related to the fact that IBM's SQL (and most others)
  do not enforce the concept of a primary key.

>A unique ID does solve a large number of problems.


  Here is a quote from Chris Date's book "A Guide To The SQL Standard,
  Second Edition" (Addison-Wesley 1989).  
 
  Appendix F.9 - Aspects of the Relational Model Not Supported
 
  "Primary keys provide the SOLE (emphasis added) record-level
   addressing mechanism within the relational model.  That is, the
   ONLY (Date's emphasis) system-guaranteed method of identifying
   an individual record is via the combination (R,k) where R is
   the name of the relation and k is the primary key value for the
   record concerned.  Every relation (to BE a relation (Date's emphasis))
   is required to have a primary key.  Primary keys are (of course)
   required to be unique; in the case of real (i.e., base) relations,
   they are also required to be wholly non-null.
 
   Comment: In fact, of course, the relational model requires every
   relation to satisfy the requirement that there be no duplicate
   rows (this is implied by the primary key requirement).  The fact
   that SQL does permit duplicate rows should be regarded as another
   grave mistake in the original design of the language. End comment.
 
   SQL currently provides the mechanisms that allow users to apply
   the primary key discipline for themselves, but it does not understand
   the semantics associated with that discipline. As a result, SQL
   support for certain other functions is either deficient or lacking
   entirely, as I now explain.
 
   <some stuff deleted here>

   3. An understanding of primary keys is required in order to support
   the updating of views correctly.  SQL's rules for updating of views
   are in fact disgracefully ad hoc.  <stuff deleted>
     (a) Projections are logically updatable if and only if they preserve
         the primary key of the underlying relation.  However, SQL supports
         updates not on projections per se, but on what might be called
         column subsets where a column subset is any subset of the
         columns of the underlying table for which duplicate elimination
         is NOT requested - with a "user beware" if that subset does not
         in fact include the underlying primary key.  
     (b) Any restriction should be logicially updatable.  SQL however,
         does not permit such updates if duplicate elimination is requested.
         <lotsa stuff deleted here>
     (c) A join of two tables on their primary keys should always be
         updatable.... However SQL does not currently allow updates on
         any joins at all. 

(End of Date's excerpt)          
 
Anyway, Date's view is consistent with what I have been posting to Kyle
and other threads.  Primary keys are REQUIRED by the relational model
and are the only dependable mechanism to update rows in a table.  
 
I used to think this was a flaw in the relational model (i.e., you know,m
these math guys don't understand the real world, where there are duplicate
rows, abscence of keys, etc).  I have come to understand, however, that
it is really a failing of us programmers to recognize that the math guys
are right and that we need to insist on more rigor in the definition of
our data bases so that the relational model can be used properly, rather
than coming up with kludgy ideas like updatable cursors, etc.  
 
Jon Rosen
===================================================
"I know I put it in here somewhere!!"
===================================================