Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!swrinde!ucsd!pacbell.com!decwrl!pa.dec.com!hollie.rdg.dec.com!decuk.uvo.dec.com!nntpd.lkg.dec.com!refine.enet.dec.com!saunders
From: saunders@refine.enet.dec.com (Kyle Saunders)
Newsgroups: comp.databases
Subject: Re: how to implement cursor based UPDATE/DELETE
Message-ID: <22685@shlump.lkg.dec.com>
Date: 15 May 91 14:26:30 GMT
Sender: newsdaemon@shlump.lkg.dec.com
Organization: Digital Equipment Corporation
Lines: 76


In article <22637@shlump.lkg.dec.com> jfr@locus.com (Jon Rosen) writes:
|In article <22637@shlump.lkg.dec.com> saunders@refine.enet.dec.com (Kyle Saunder
s) writes:
|>|>     I was wondering how you would implement cursor based UPDATE's and
|>|>DELETE's.
|>|> <PREVIOUS POSTS DELETED>

|>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"

	No, I was stating that I felt allowing the user to see the internal
row-id, such that it is, would be violating the Relational Model.  You are
right that the Relational Model says nothing about the physical representation,
except that the user should _never_ see it.

|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

	This is what I was referring to above.

|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

	I know that requiring requiring an index would violate the Relational
Model.  That is why I did not want to do that.  That is why I suggested that
perhaps the RDBMS should just create the index on the fly, _transparent_ to the
user, and drop it when finished.

|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).

	Thanks for the lecture.  I already knew all that.

|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
|<

	You are missing the point, I think.  When you create a temporary
table with DECLARE cursor CURSOR FOR query; OPEN cursor; the columns in the
above query may not include all columns or the primary key.  Therefore, in 
order to find the row in the base table that corresponds to the row in the
temporary table, you need to have an index on one of the fields that is 
included in the query.

	- Kyle