Is it best to have one PK in a table and that as an autonumber?
If so, why do people create composite PK's. Examples please.

Oh, aziz, you have hit upon a question that, within a database community, bears almost the same level of interest and venom as the choice between Islam and Judaism as a personal religion.

In this forum, you can search for the topic "Meaningless Key" to see a thread that deeply explores this issue.

The SHORT answer to simple autonumber vs. compound key has to do with how you use the keys and how large they are. It has to do with style issues. It is also one of whether the keys in question cause you to have a conceptual problem when you use them - i.e. do the keys have so little meaning that using them makes it harder to visualize what you are doing?

The question also impinges on the presence of a candidate key for being the prime key. (Whether simple or compound...) If there IS no viable candidate for a simple or short compound PRIME key (i.e. no duplicates) from among the values in the table, then you are looking at a simple autonumber for lack of something better. When a short candidate is available, it makes sense to use it rather than a meaningless number, again regardless of whether the candidate is simple or compound. When the only candidates are very long, you have to consider that long keys are highly inefficient and might be served better by a simple autonumber key.

An example of the latter: Suppose you have product numbers that are cast in stone and are flat guaranteed to be unique - and they fit in a LONG. Well, never mind autonumber, you have your key right there! Now consider a contrary case - the product number isn't unique until you add a modifier. The size of the number and the size of the modifier affect the key size, which in turn affects the number of keys per buffer you can store in the index for that table. So depending on the modifier, you might need as much as TWO numbers of type LONG as your compound PK. Well, that might be worth it.

OK, go for broke. The candidate key is absolutely unique but let's say it is a very long character sequence with numbers, letters, dots, dashes,... and it takes up 20 characters. That is 5 times the size of an autonumber. If you are going to ever use this PK to link to other tables, you are multiplying the size of the tables by the size of the PK, which is now becoming unweildy.

Where is the cutover point? It depends not so much on a harsh yes/no test as basically what you will tolerate.

Going with autonumber PK, you are assured of a relatively small, unique key that has nearly no meaning whatsoever. If you can live with that, use it. If you cannot, you must find a simple or compound candidate key on your own.

Example of a simple compound key.

In a major database I wrote and am responsible for, I have a list of PINs (LONG) that can repeat, so that alone cannot be the key. However, each PIN can only appear in each Measure (BYTE) and Market (BYTE) once. There's your key. For example:

PIN Market Measure
1234567 1 1
1234567 1 2
2345678 2 1


Without using a compound key, 1234567 would be a duplicate key, and you cannot have that. Also, because the key is a LONG (like an AutoNumber) and we're just adding two BYTES, it's not unnecessarily huge. And finally, PIN, in my industry, is a very useful piece of information to have. Using just a PIN, we can look up contract rates, all the markets/measures where they are active, etc. If we had used an AutoNumber, we would've had to associate it back to a PIN (an extra, unnecessary step), and because PINs can change over time, we cannot say that an AutoNumber will always be associated with one PIN (more overhead to maintain).

That's the sort of thing you want.

AutoNumbers are good if whatever else you're making part of the key cannot be used to go back to the original data. Otherwise, they're extraneous information.

Thanks guys. I'll need to digest all that. Personally prefer a simple SINGLE autonumber field.