>>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

I use a primary the which the user never sees. This key is integer, and "auto-generated".

Main advantages:

Only 4 bytes in main table - hardly an advantage, since I need the SSN anyway.

Only 4 bytes for reference in other tables.

In all cases, a single field can be used for PK or FK.

This fact simplifies relations.

The short (and single-field) key makes joins faster.

HTH, Hilmar.

Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)