A reader, April 02, 2013 - 12:27 pm UTC
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.
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??
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
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 ?