Plausability of natural key as primary key
Home > IT Answers > Database > Plausability of natural key as primary key
Bdpowell
25 pts.
0
Q:
Plausability of natural key as primary key
Primary keys, Natural Keys, Surrogate Keys
If you have a field in a table that is a natural key and meets all the criteria for a Primary key, is it advisable to use that natural key as your Primary? Or is it a better idea to assign a surrogate key (i.e. autonumber for TrainingProgramID instead of using TrainingProgramName)? Thanks in advance for your help!
ASKED: Mar 31 2009  12:08 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Mrdenny
46795 pts.
0
A:
 RATE THIS ANSWER
+1
Click to Vote:
  •   1
  •  0
  • AddThis Social Bookmark Button
Personally, I prefer to use surrogate keys.

If there is some chance that the Program Name could change in the future (and names are usually subject of change as the business changes), then I would strongly recommend to use a surrogate key (Program ID).

----------------------------------

If there is a natural key which makes sense to use as a primary key then use the natural key. In your example TrainingProgramName probably isn't is the natural key, but because the value can change it doesn't make a good primary key. Now if you were working for the IRS and you had to design a table which held tax payer information, there would be a natural key that you could use. The tax payer id (it being tax time right now, I'll try and work it into my examples) would make an excellent natural primary key. Each person has one, and only one. And they are rarely changed.
Last Answered: Mar 31 2009  5:21 PM GMT by Mrdenny   46795 pts.
Latest Contributors: Carlosdl   29820 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   29820 pts.  |   Mar 31 2009  2:25PM GMT

Additionally, if you need, say 100 bytes to store the program name, and 10 bytes to store an Id, then you would save some storage space if there are many tables with foreign keys referencing this table.

 
0