Hi Renoir,

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


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

Well in order to so, you must be sure that the SNN is available for each single record because its the PK. Also you must be sure that it's entered correctly and is not going to change or you must propagate the changes in child tables with a cascading update. If there is any doubt that the SNN will meet these criteria, you might save yourself some nightmares by using a surogate key.

OTOH,if PKs are stable and entered correctly and are not going to change in the future or seldom changes can be handled with cascading updates, there is no real reason not to do so. It can give you a performance advantage when reducing the number of joins in SQL statements.

Walter,