I have read many articles now about natural vs surrogate primary keys, and came to the conclusion there is no single best practice.

I have a table that will have around 2000 definite unique values, each value will range from 5 characters to 40 in length.

This seems like a partial choice as a natural key, although the values which are 40 characters in length may cause some performance and storage issues when they are referenced elsewhere.

As the total maximum rows in this table is fixed as 2000 and 35% of these rows contain value length of 25-40 characters(65% have length 6-25), shall I go with a natural key here?

With your experience, what would you do here?

  • 3
    2,000 rows is not going to cause any storage or performance issues. Go with the natural key. Worry about performance and storage when you have 2,000,000 rows. Mar 4 '14 at 2:08

As a general rule every table ought to have a natural key because it usually isn't a good idea to duplicate information in a table. Only add a surrogate key if and when you find there is a reason to do so.

  • I don't think that information is duplicated. One cannot extract the value of the natural key from the surrogate key nor the surrogate key from the natural key.
    – miracle173
    Mar 5 '14 at 8:49
  • so what is the answer to the OP. should he or should he not use surrogate keys?
    – miracle173
    Mar 5 '14 at 9:02
  • @mircale, my point was that the reason for key constraints is to guarantee that infomration is not duplicated. If you don't enforce a natural key on some attribute then the values of that attribute may be duplicated on multiple tuples with different surrogate key values. That is apparently not what is wanted here.
    – nvogel
    Mar 5 '14 at 10:22
  • I think I understand what you mean: This natural key should be either the primary key or a unique consraint for this natural key columns should be defined if a surrogat key is used.
    – miracle173
    Mar 5 '14 at 10:35

First of all, for 2000 records (as others have stated) everything will work. So, for the OP the natural key will still work.

On the other hand VARCHAR fields for primary keys are (in most of the cases) a bad idea. They are inefficient, hard to index and provide slow performance. In most of the cases a numeric field ( int / bigint) will probably work better.

So, in the argument natural vs surrogate, the correct answer is: It depends. Stick with the natural key if it fits your and your app purposes. If not, use a surrogate.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.