Re: ID field as logical address

"Bernard Peek" <bap_at_shrdlu.com> wrote in message news:QFOfTQMZc+HKFwIo_at_shrdlu.com...
> In message <xWQTl.1481$9L2.793_at_nwrddc02.gnilink.net>, Walter Mitty
> <wamitty_at_verizon.net> writes
>
>>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.
>
> This appears to be an update anomaly but it's not as simple as that. If we
> think of the logical data structure the ID isn't in the same entity as the
> data we want to manage. It's in a separate entity with only one attribute.
> There is supposed to be a 1:1 relationship between the ID entity and the
> data entity, but this is not usually maintained by the database. The
> cardinality of that relationship is managed either programmatically or by
> external business processes.
>

Bongo! I think you got the nail on the head.

The problem is that certain tools that I'll call "application generators" blur the distinction between an ID field and the entity that it serves as an indirect identifier for.

In particular, MA Access, a fairly popular application generator, has a a datatype called "autonumber". If you make your primary key an autonumber field, several things happen.
first, if you let a forms wizard build your data entry form for you, it will omit the autonumbered field. Good idea, since you can't specify in anyway. If you get in there with SQL and try to update the autonumber field, Access will flag you with an error. I could go on.

So, even if the relational model doesn't forbid updates on any attribute, the neophyte learning on MS Access is apt to learn that autonumber fields can't be updated.

I don't know what the situation is for SQL Server. My Oracle knowledge is out of date. Received on Fri May 29 2009 - 18:13:04 CEST