Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!swrinde!elroy.jpl.nasa.gov!turnkey!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: <24356@oolong.la.locus.com>
Date: 14 May 91 18:47:57 GMT
References: <22637@shlump.lkg.dec.com>
Organization: Locus Computing Corp, Los Angeles
Lines: 47

In article <22637@shlump.lkg.dec.com> saunders@refine.enet.dec.com (Kyle Saunders) writes:
>|>	I was wondering how you would implement cursor based UPDATE's and 
>|>DELETE's.
>|> <PREVIOUS POSTS DELETED>
>	Well, your solution is exactly what I am trying to avoid.  I'm
>_writing_ the SQL RDBMS, so an SQL work-around is not what I need.  It looks
>like I will have to require an index to be created on at least one of the
>field in the query definition of the cursor.
>	  I want to avoid such kludges as the "hidden" ROWID column that
>INFORMIX evidently has.  It doesn't provide for the physical abstraction that
>the Relational Model is supposed to have.  I suppose you could say that
>requiring an index also violates the relational model, but something tells me
>that when you do:
>DECLARE cursor-name CURSOR FOR select-query FOR UPDATE [optional-fields]
>most RDBMS's will create an index on the fly if one doesn't exist on the
>fields to update.  I would do this too, but I don't implement indexes yet.

I see what you are looking for now.  You need an internal implementation.
Your statement about the relational model requiring physical abstration
is off-target.  The relational model says nothing about the physical
implementation and in fact, there is absolutely nothing about the "right"
and "wrong" ways to implement a relational data base.  The relational mode
er model requires complete logical abstraction.  The user's VIEW of the
data must be relational.  The underlying implementation can do ANYTHING 
it wants or needs in order to create a complete and potentially efficient
view of the relational model.  In fact, this almost ALWAYS involves 
internal tuple ids or row ids.  The issue is whether these internal ids
are externally manifested (this would appear to be in violation of the
relational model although some argue otherwise).  REQUIRING an index 
violates the relational model (you should always be able to do any operation
solely by reference to the table's data).  However, REQUIRING a PRIMARY KEY 
is, in fact, intrinsic in the relational model.  The relational model
DOES NOT ALLOW FOR DUPLICATE ROWS IN TABLES.  Thus, every table ALWAYS
has a primary key, even if it consists of every attribute (column) 
of the row.  Keep in mind that PRIMARY KEYS are NOT INDEXES (even though
almost every physical implementation that enforces primary keys implements
them using some kind of indexing or hashing mechanism for performance).  

Your choices for uniquely identifying rows are limited to either the
primary key (no matter how many columns that involves) or an arbitrary
row id that you keep for each row.  If you are going to allow duplicate
rows in your table, you can ONLY use a row id since there is no way to
know which duplicate row is which since there is no primary key where
there are duplicate rows.  In effect, the row id becomes an arbitrary 
primary key.  No matter how you dice and slice it, that's all there is.
 
Jon Rosen