Surrogate key 'preference' explanation

Question

As I understand there is a war going on between purists of natural key and purists of surrogate key. In likes to this this post (there are more) people say 'natural key is bad for you, always use surrogate...

However, either I am stupid or blind but I can not see a reason to have surrogate key always!

Say you have 3 tables in configuration like this:

Why would I need a surrogate key for it?? I mean it makes perfect sense not to have it.

Also, can someone please explain why primary keys should never change according to surrogate key purists? I mean, if I have say color_id VARCHAR(30) and a key is black, and I no longer need black because I am changing it to charcoal, why is it a bad idea to change black key to charcoal and all referencing columns too?

EDIT: Just noticed that I dont even need to change it! Just create new one, change referencing columns (same as I would have to do with surrogate key) and leave old one in peace....

In surrogate key mantra I need to then create additional entry with, say, id=232 and name=black. How does that benefit me really? I have a spare key in table which I don't need any more. Also I need to join to get a colour name while otherwise I can stay in one table and be merry?

Please explain like to a 5 year old, and please keep in mind that I am not trying to say 'surrogate key is bad', I am trying to understand why would someone say things like 'always use surrogate key!'.

Answer 1

Surrogate keys are useful where there is an suboptimal natural key: no more, no less. A suboptimal natural key would be a GUID or varchar or otherwise wide/non-ordered.

However, the decision to use a surrogate is an implementation decision after the conceptual and logical modelling process, based on knowledge of how the chosen RDBMS works.

However, this best practice of "have a surrogate key" is now "always have a surrogate key" and it introduced immediately. Object Relation Mappers also often add surrogate keys to all tables whether needed or not which doesn't help.

For a link (many-many) table, you don't need one: SQL: Do you need an auto-incremental primary key for Many-Many tables?. For a table with 2 int columns, the overhead is an extra 50% of data for a surrogate column (assuming ints and ignoring row metadata)

Answer 2

Well, I am more on the natural keys myself :)

But surrogate keys can have its advantages, even if you like me want to go "natural" all the way :)

For example, I have a table that, due to various constraints, has to be defined as being dependent from others. Something like

Table Fee (
foreign_key1,
foreign_key2,
foreign_key3,
value)

the record is defined/identified by the three foreign keys but at the same time, you can have at most 2 of them to be null. So you cannot create them as a primary keys (u'll just put an unique on the 3 columns) In order to have a primary key on that table, the only way to do that is to use a surrogate :)

Now... why not to change a primary key... This can be considered pretty philosophical... I see it in this way, hope it will make sense... A primary key, in itself, is not only a combination of unique+not null, it is more about "the real essence of the record", what it defines the record at the core. In that sense, it is not something you could change easily, could you?

Consider yourself as an example. You have a nick, but it does not defines what u really are. You could change it, but the essence of being yourself would not change. Now, if you maintain the nickname, but change your essence... would it still be the same person? Nope, it would make more sense to consider it a "new" person... And for records it's the same...

So that's why you usually do not change the primary key and define a new record from scratch

Answer 3

Always remember surrogate key is the additional column for our actual table columns let us take a table columns like below

patient_name
address
mobile_no
email_address

See here imagine we are working with admission of patient records so here we can't take mobile_no has primary key because we can take but some people might not have mobile no instead of this go for surrogate key and make it as primary key and make actual mobile_no, patient_name as primary key then we can easily perform ..here if mobile no changed no problem we can still search with the help of surrogate key like below..

Here you can write surrogate key on the top of actual data

patient_no----->primary key[surrogate key]
patient_name ---->pk
address
mobile_no--->pk
email_address