Skip to Main Content
  • Questions
  • Natural Key as Primary Key Vs Surrogate Key

Breadcrumb

Easter

Question and Answer

Tom Kyte

Thanks for the question.

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

Answered by: Tom Kyte - Last updated: July 16, 2013 - 4:08 pm UTC

Category: Database - 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 we 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.


and you rated our response

  (3 ratings)

We're not taking reviews currently, so please try again later if you want to add a review.

Reviews

April 02, 2013 - 12:27 pm UTC

Reviewer: A reader from India

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

Followup  

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

July 12, 2013 - 12:07 pm UTC

Reviewer: sasanka ghosh from India

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

Followup  

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

March 24, 2014 - 7:48 pm UTC

Reviewer: Kumar from OH

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 ?