>>A primary key is unique and identifies a specific record. A candidate key is also unique, however it relates to an entity other than the primary key. An example would be a customer code, social security code, etc. If a violation of uniqueness occurs due to a duplicate – the system user must know. It has been known to have duplicate social security numbers issued. When this occurs it must be resolved. Hope you get the general concept.
>
>Thomas,
>
>PMFJI, but do you mean that if I have a uniquie control number for each record that could be the primary key, but if I also have a business need to make sure the social security number is unique, that would be a candidate? Silly question, I'm sure, but when do you need both? Why not just use the SSN?
>
>Renoir

Renoir;

Not a silly question at all. The social security service is famous for issuing the same number to more than one person. In fact I have seen this occur in real life. I consider a primary key to be truly a unique system generated value. With the work I do I will have a primary key for each table.

Any other requirement of a “unique value” such as in my example a customer code or social security number becomes a candidate key. I consider primary keys and candidate keys to have very different reasons for existence. The primary key “belongs” to the table while the candidate key “belongs” to the business entity. It is truly a matter of choice when all is said and done. Having worked with this for many years I do not think about it when creating a new table.

You could:
1. Use no unique keys.
2. Use (as an example) social security numbers as primary keys

If a user is required to enter the value of the primary key such action is more likely to generate an error (typo, etc.) than if a system automatically generates a unique value. This is just a probability concept. I have seen this occur several times and then you must be able to handle the error.

A user can just as well enter a duplicate value (typo, etc.) for a candidate key. Should that occur you still have to resolve the issue. There is less probability of an error at this point but it is possible.

Normally candidate keys are formatted in some way – consecutive numbers, letter/number combinations, hyphens at specific points, etc. and some may even be randomly formatted.

Each developer will use whatever concept seems best to him/her. Doing it one way or another may only be a matter of rationalization. How you like to do this is up to you. I think the important thing is to be consistent rather than academic. What works for you is best!

Tom