Skip to Main Content
  • Questions

  • Natural Key as Primary Key Vs Surrogate Key

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: February 12, 2013 - 11:36 am UTC

Last updated: July 16, 2013 - 4:08 pm UTC

Version: 10.1

Viewed 10K+ times! This question is

You Asked

Hi Tom - It will be great if you could share your views :

1.Disadvantages (if any) of using Natural Keys in tables as Primary Key in OLTP systems.
2.Is a composite natural primary key of few columns in a table a bottleneck in terms of performance of select statements/joins.
3.Does a composite natural primary key of few columns require more space.


-Thanks as always.


and Tom said...

1) http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:689240000346704229

2) no, numbers in Oracle are varying length fields of 0 to 22 bytes - just like a string... Not much difference there.

Just a side note here:

I'm loving the other databases that use a 4 byte integer. The day those databases all break is just around the corner.

Used to be 20 years ago that a 1 million row table was 'big'

Now billion row tables are becoming very very very common. Tables with multiple billions of rows are right around the corner.

and those 32bit signed integers (even the unsigned ones) are going to start breaking very soon... (they already have, repeatedly)

3) figure that you have to add in:

a) the extra space for the surrogate. the surrogate wouldn't be there if you used the natural key.

b) the fact that the index on the natural key *will still exist* in both scenarios because it is unique after all and that must still be enforced

c) the fact that the surrogate key will have an index on it

you end up using more space with a surrogate on that table singe you've added the surrogate to the size of the table and an extra index that wouldn't exist otherwise.


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 02, 2013 - 12:27 pm UTC

Hi Tom,

I am still not clear on the Surrogate Key vs Natural Primary Key. Could you please explain in some detail where exactly we can call a key as Surrogate / Natural Primary key.

Thank you very much in advance.

Thanks,
Santhosh
Tom Kyte
April 22, 2013 - 1:24 pm UTC

a natural key is an immutable set of attributes that uniquely identify a row that occur naturally with the row itself.

a surrogate key is an immutable set of attributes that uniquely identify a row that were generated specifically and soley to identify this row (they did not occur naturally)

google up natural key, you'll find lots of definitions for it.

natural keys happen, but they are very rare in real life.

surrogate key vs natural key in OLTP

sasanka ghosh, July 12, 2013 - 12:07 pm UTC

Hi Tom ,
I agree with your points. But the question arises when these multicolumn primary keys become foreign keys to other entities. then one is taking up lot of space ( particularly I am concerned abt the reference data with versions ).
say as an example in a reference data management architecture with versioning product category is an entity.
we need to keep a SEQ column as part of primary key and this SEQ will be repeated everywhere in every child entity and so on.
in this case what will be the best practice??
Tom Kyte
July 16, 2013 - 4:08 pm UTC

define "lots of space"

when you add it up, it will not be terabytes in all probability - or even 10's of gigabytes.


do you think you'll ever need that seq column in the child? wouldn't it be nice to not have to join to get it when you do?

SSN natural vs surrogate key

Kumar, March 24, 2014 - 7:48 pm UTC

Tom,

I take this opportunity to thank you for the great work you have been doing.

My question, is SSN a natural key or surrogate key. I keep reading from multiple sites thru' Google that SSN is a Natural key.

To me, SSN is again computed from a formula/ sequence and hence it is not a natural key.

If asked I would say, biometric info is the best natural key. Can you please explain if I'm correct ?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.