Choosing a primary key

[sbayeta]

Hi,

I have a field 'lote' containing unique values like

TM07082004
TT07082004
TN07082004
TM08082004
TT08082004

I'd like to know whether I should use this field as primary key for my table, or create an autonumeric field and use that instead.

What are the drawbacks/advantages of each approach ?

Thanks a lot.

Note that the existing 'lote' field can't be eliminated.

 

[jjc_nj]

If the field is required (you mentioned it was) and each value is unique, I would use the field itself for the Primary Key as opposed to creating a auto-number.

In general, if you have a fileld is unique and can be used as a identification for a record, go with that field as a PK.

 

[WayneRyan]

s,

I'd break your field into its two parts. And use an Autonumber as the
primary key.

http://www.access-programmers.co.uk/forums/showthread.php?t=70826

Wayne

 

[smercer]

sbayeta: I noticed that the primary key contains the date.

The two first letters, if there is a need for the same two letters on the same day it would not be unique.

Also consider that the alphabet contains 26 characters, multiply that by 2 and you get 52 different entries that would be unique. You can not have anymore than 52 different entries in one day with this primary key.


The disadvantage with the autonumber is that you cannot tell by looking at the number which record it relates to.

In my data base, my primary key goes like this:

CP-PR-DEI-4

The CP is a category of book "computers"

the PR is a sub category of book "Programming"

DEI is the first three letters of author's surname

the number on the end is a number that the autonumber made up (in a different field) and I combined them together.

I am considering to add a third letter to the categories for the above reason

I hope this gave you some ideas.