Re: Autonumber in Oracle

Mike,
[Quoted] Several others have already suggested using a sequence for the autonumber functionality similar to MS Access. I wanted to address the second part of your question.
[Quoted] There are advantages to using a real column value as PK if the value meets the requirement of being unique for the table. In many cases when real column values are used, the PK does end up being a compound key, such as PartNumber+VendorID, as in your example. One of the advantages is that the actual number/value often means something to the user. For example, the counter guy at an automotive parts store can often tell a lot about a part by looking at the PartNumber and Vendor. Information is carried in these keys. This makes troubleshooting applications written on the database much easier, as errors are easier to spot. The PK can be displayed in forms and reports, as it adds value to the user. Sequences are totally meaningless as data. Their best use as I see it is to provide a layer of indirection for an application, when needed, such as the EUL tables that Oracle Discoverer uses. These tables manage metadata, so the application design requires such abstraction, and a sequence provides a reliable PK for each row. .
But consider your real-world table example with PartNumbers and VendorIDs. The same table could be created with a column added for an autonumber or sequence as the PK. If so, each row in the table will carry one more additional field. I don't know about you, but in the database I work on that is tens to hundreds of millions of rows. Sure, disk is getting cheap, but that is a lot of extra data. Also, each one must be filled with a call to the sequence, adding complexity to the application code and impacting performance to some degree (especially if you use sequences all over the database).

If a relational entity is designed correctly, there is always a combination of attributes which is unique for a tupple. (Isn't that what first normal form is all about?) The question to ask is "What advantage to the application is gained by using a column filled with a Sequence, instead of a real data column(s) as Primary Key?" If you do not have a good answer, use the real column values as PK--they will have to be carried in the table anyway, because they are data.
IMHO,
Craig

Mike Phillips wrote in message
>Question: Is there autonumber functinality in oracle like in access? Also,
>is it still a good idea then,
>to create a separate artificial recordID (autonumber) column for each table
>as the primary key. Or should we actually use real column values, for eg.:
>the combination of 'PartNumber+VendorID' values as the primary key for the
>tables, keeping in mind that in the end all records in a relational table
>must be unique. I personally liked the autonumber
>idea in MSAccess.
>
>Mike
>
>
>
>
Received on Fri Sep 24 1999 - 07:08:31 CEST