Primary Key or Unique Value Index?

[PaulA]

I have set up tables that have a single field primary key (usually auto-generated) and the a multiple field index with "Unique" set to yes to avoid duplicate records.

Is this the best way or should I have the mutliple fields (usually no more than three) be the primary key?

Thanks.

 

[JustMaybe]

not sure this is what you mean..but..

each table must have a unique primary key. One table can only have one primary key. This key can logically be an 'autonumber'. By adding a primary key to a table you ensure no records can be duplicated becaude there can only be one instance of that field value.
Autonumber just means that the computer finds the next free 'unique number' rather than you having to do it. therefore setting this format will ensure that these records can't be duplicated. Erasing the need to use multiple fields.

 

[PaulA]

Thanks for your response--

I understand what you said, but if multiple fields (with meaningful values) are required to assure a unique record, should thoses fields become the primary key or can you just set up an autonumber record identifier as the primary key and have a multifield index that requires unique values in all the identifed fields? This index would not be considered primary.

What is best for the database to understand and manage?

Thanks.

 

[mdemarte]

benefits of autonumber primary key

I just finished converting a few databases from Approach to Access with many to many relationships.

For the junction table, I could have used the primary auto-number key from one combined with the primary auto-number key of another as my multi-field primary key in the junction table, but I found that to be confusing. I prefered to use a separate auto-number primary key. I can follow my relationships easily that way, too.

One benefit to this is that if someone adds a record later, I can look at the auto-number primary keys and see that happened. This has helped me track down problems with a report printed yesterday that does not match a report printed today. Why? Cause of that added record to the junction table.

Another benefit is that I can sort entries by that auto-number field when creating a report, so that the input form matches the screen form matches the report. Sorting by the multi-field primary key MIGHT give you that order or it might not.