Autonumber as primary key

[FrustratedStudent]

I have heard that a autonumber key is not a good primary key to use as a primary key, but if you have planned all relationships correctly will there be a problem in the future???

Also, why is it incorrect to use this? You know the benefits are having a unique identifier for each record. A potential problem is that you don't have any control over it.

Thanks

FRUSTRATEDSTUDENT

 

[Robertd]

Hi FrUsTrAtEdStUdEnT,

nonsense, an auto number field works well as a primary (or any other) key. The point you make is correct, you have sod all control over the autonumber field. from a relational database point of view it is a "forien" key in that it has nothing to do with the object you are describing as a record it is introduced to give each record a unique id and as such doesn't describe anything of the record save a unique ID.

as for related tables there is no difference between an "autonumber" data type and any other key field type, infact the data type long integer is an exelent chioce for a key field. ofcourse it would be nice if the model you were describing had it's own unique identity say a serial number or such even then there are problems. consider a database that kept records of invoices from various suppliers whilst it would be unlikely that different suppliers would have the same invoice number it is posible,... here i would key the invoice table with an autonumber field and create a unique index with the Supplier ID and Invoice Number allowing a duplicate invoice number only if the invoice number was from a different supplier.

eg
SupplierInvoice (table)
InvoiceID Autonumber
SupplierID long Integer
InvoiceNr Text. 25
other data
Key InvoiceID
Unique Index SupplierID AND InvoiceNr

SupplierInvoiceItems (table)
InvoiceID Long Integer
ItemID Text. 25
Description Text 50
other data
key InvoiceID AND ItemID

It is true that there are some "idiosyncrasies" such as once an autonumber ID is issued that "number" has been commited ie if you add a record then decide to cancel the entry then the ID issued is lost. ie the ID cannot be reissued (there are ways arround this but from a practical sense once issued the ID can not be reissued, ever,...)

be aware an autonumber field is an alternative, sometimes
its a second choice others its a first choice.

hope this answers your question, the choice of key field depends on many factors and largely depends on what the table needs to describe and what existing unique identifiers exist in the tables fields.

ie Social Security number must be a unique ID.

bottom line "Autonumber fields work well" ;-)

hth


Robert Dwyer
rdwyer@orion-online.com.au