Re: ID field as logical address

"Walter Mitty" <wamitty_at_verizon.net> wrote in message news:xWQTl.1481$9L2.793_at_nwrddc02.gnilink.net...
> In another thread, I suggested that prefixing every tuple with an
> auto-generated field (column) called ID amounted to reference by location
> rather than reference by content. Some people correctly point out to me
> that there is a level of indirection gained by using ID fields instead of
> pointers. That means the records (rows) "pointed to" by references to
> the ID are not pinned, and can still be shuffled. That's good, but it's
> trivial, IMO, when compared to the advantages claimed by Codd and refined
> by Date for the Relational model over alternative models. As far as I can
> see, a database where every foreign key refers to an auto-generated ID
> column is just a graph database disguised to look like a relational
> database.
>
> In this thread, I want to further explore the use of ID fields, and
> approach the question of whether using ID fields is relevant to the
> discussion of whether an UPDATE is somehow different from a DELETE
> followed by an INSERT. I declined to engage Brian in this question when he
> posted a response in the earlier thread. I'm willing to dialogue about
> that here.
>
> Let's say that the design has followed the practice of including an
> auto-generated key called ID in every table, and declaring the ID as the
> PRIMARY KEY. Let's further assume a practice that says that you never
> update the ID field of any tuple, once it's been assigned. Now let's say
> you have a table that records values that look like (ID, value). In an
> actual case, value would probably ityself be a tuple, so our tagged
> tuples might look like, for example, (ID, LastName, FirstName,
> PhoneNumber).
>
> Now, if we update the LastName, the FirstName, and the Phone number of an
> entry, it's still the same entry, because the ID field retains the same
> value.
>
> Now what happens if instead of updating the three dependent fields, we
> delete the row, and insert a new one. Here, I'm making an assumption
> about how auto-generate works. I'm assuming that, once used, and ID will
> never be auto-generated again, even if it's no longer in use. So, when
> we delete a row and insert a new row, we get a brand new ID. All of the
> references to the deleted row remain as orphans, unless they got deleted
> by a cascaded delete.
>
> So, Brian's claim that deleting a value and inserting a new one is somehow
> really different from updating an existing value works in this model, if
> you regard ID as somehow "special", and not "part of the value recorded
> in the table".
>

Just to be clear, one can assert what in the world is different and how, but one cannot "update" a value. Delete, update and insert are primitive assertions that state, in the context of what has up to now been the case, what has just ceased to exist, what has just changed in appearance and what has just come into existence, respectively. A value is the output of the valuation function which under an interpretation maps the terms of a formal language to things in the Universe of Discourse; therefore, each value is fixed at the instant of interpretation and therefore not subject to modification.

It is not that ID is somehow "special;" it is that each ID symbol permanently identifies (rigidly designates) something in the Universe of Discourse. Note that I am not arguing for or against the introduction of autogenerated ID fields, but when they /are/ introduced, they are not pointers: they are names. They are names in the same way that a sales order number names a particular sales order; they are names in the same way that a particular VIN names a particular vehicle. Contrast that with instances of compound keys (and even some unary keys), which are descriptions rather than names. Received on Sun May 31 2009 - 08:52:25 CEST