>>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
Oh, I forgot what I actually consider the main advantage. The user can change the SSN - or any other code, visible to the user, that identifies a record (examples: client number, product code), without propagating changes to other tables.
And of course, there are disadvantages, as well. Programming can be more cumbersome in some cases. Codes visible to the user have to be looked up - this makes some things slower.
But I firmly believe that the advantages outweigh the disadvantages. I used to program without these "surrogate keys" a while ago. My previous boss told me "You are violating a key database concept". It took me a while to see the advantages, but now you can consider me a "convert".
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)