Plausability of natural key as primary key

25 pts.
Tags:
Natural Keys
Primary 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!

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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.
    69,175 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following