How many PrimaryKeys should a table have?

[Accessme2]

Hi everyone,

Is there a magic max number of primary key a table should have or just as many as it takes to make the record unique.

Thanks

 

[Calvin]

One is generally enough for most processes and new processes. Only when the process requires it, for example working with legacy data collected from an old process, have I needed to use two or three PK's to uniquely ID a record and the new process would not allow the concatination or changing of the legacy data into one field.

 

[Mile-O]

You can only have one primary key per table.

 

[Calvin]

Mile-O-Phile,
Check your Doc's, and try an example yourself. You can assign multiples, for example sometimes a phone number field is not unique enough, in conjunction with a zip code field the two fields can be used together as PK's to uniquely ID the record.
And doing so will allow the same phone number to be listed multiple times but not in the same zip code, and vise-versa.

 

[Mile-O]

I know - two or more fields become a composite primary key. There may be up to 32 fields used to form one primary key.

 

[Calvin]

Mile-O-Phile,
Then why didn't you say that before, you only confused the issue by saying there can only be one PK, yes there is one PK definition, but 32 fields defined in the PK would have been the correct answer. You know the guy is asking how many fields can be used in a PK.

You've been on this long enought to know how to interpret questions from beginners, you should know how to post answers to beginner.

 

[Calvin]

Thanks Pat, great answer.

 

[Mile-O]

Jet only supports 10 columns in a compound pk or index.

So it does. Where did I get 32 from?