Hi,

I am having a little trouble in understanding what really is a surrogate key. Can someone kindly explain with a brief example.

Many thanks.

A natural key is a value that has meaning to the user, but ought to be unique for every row. A good example of a natural key would be a license plate number for a car.

A surrogate key is an artificial value that has no meaning to the user, but is guaranteed to be unique by the database itself. An example of a surrogate key would be an arbitrary, unique integer that was added to the license plate table to allow for the fact that a license number might be reissued (for example, some states allow vanity plates to be retired for 5 years, then reactivated).

-PatP

i would modify the definition slightly: a natural key is a key which has values taken from some domain defined outside of the database

license plate is a very poor example

a license plate such as the ones used by car dealers (hung from the back of any car to allow it to be driven on the street) in no way identifies which car it is

my license plate is a vanity plate (see picture, http://r937.com/thebug.jpg) but if i sell my bug and buy another car, the license plate stays with me

a much better example would be the VIN (vehicle identification number) which is a standard used by the automobile industry

note that the VIN does not necessarily have "meaning to the user"

some people would call the VIN a surrogate key, but i call it natural

you get the same discussion about ISBN (International Standard Book Number)

i would call ISBN a natural key

The VIN is definitely not a surrogate key.

A natural key could be a composite of lastname, firstname and middle initial. This has meaning to anyone who reads it. Its not the best example but each name could be unique.

A surrogate key would assign a meaningless unique number to each row containing a name. This would allow for modification of the name without changing the surrogate key. Updates to child tables would not be necessary.

The natural key would have a unique constraint. The surrogate key would be the primary key.

Both natural and surrogate keys have their uses. One needn't be a purist in the use of either.

Hi,
First do you understand what a primary key is? A primary key is essentially a unique identifier for an entitiy.
Ask yourself, how could tell 'one' of these '?' (whatever your entity is.... one of these people, one of these cars, one of these orders... whatever your entity is) How do I uniquely identify 'one' of these from all other of these in the whole universe ?

Database design books will tell you, that to do find out what a primary key is, first you evaluate 'candidates'. Look at the attributes you've defined for an entity and see if any of them, or a group of them honestly uniquely identifies 'one' instance of this entity.

The idea here, is that as you move from logical design into a physical database table, each row should represent a 'unique' one of these (whatever your entity is).

What you'll find, is that very few business entities have keys. You can look at each attribute or group of attributes.... and usually there isn't a unique identifier there. If there would be an attribute or group of attributes, you would call that a 'natural' key. This is a natural key because some entity 'naturally' has an attribute or set of attributes that uniquely identify it.

It doesn't happen very often.

So then, when you go to make a physical database table to store your entity..... how will uniquely identify 'one' instance or row ?????????

What if you create some 'generic' identifier for your entity. Some identifier that will uniquely identify each row or instance of your entity. Most moder relationship database management servers, like ORACLE, DB2, MySQL, etc.... have the ability to generate some unique number for you to do this.

Because this 'generic' identifier is filling in for a natural one, that usually doesn't really exist..... it is often called surrogate

Characteristics of Surrogate Key

A surrogate key should have NO hidden codes or meaning.

A surrogate key should be generated automatically - preferrably by the database

A surrogate key should in fact UNIQUELY identify a row in a table.

Well I hope that helps!!!
Vmusic

~Good Design Saves Time~

that's a nice post, vmusic, and i disagree with only one small point -- that business entities don't usually have natural keys

departments have names, you'll never see an org chart with two HR departments, and if you do, they're the head office HR department and the branch HR department, not the same name after all

orders have order numbers, we've all seen them, order number LXB10010 or order number 200452 or order number 73JAN2005

employees have employee numbers, and how many times have you implemented a database which uses the company employee number? all the time, and even if you might also declare a surrogate key in addition to the employee number, you still have that nice unique employee number

have you ever merged two companies? needed to add the employees of one subsidiary's payroll system to the payroll system of the parent company? did you have employee number collisions? did the surrogates help? i didn't think so

my experience is the opposite, business entities have lots of natural keys

perhaps you meant that they don't often have ideal permanent, universally unique natural keys

surrogates don't always solve those problems either

I've used an org chart with five CEOs, three IT departments, and four HR departments... Not exactly a common arrangement, but it works for them.

In an ideal world, surrogates would never have collisions. GUIDs very closely approximate that ideal.

At least for my purposes, if a user can change an attribute, I can't use that attribute in a primary key. I realize that is a rather draconian approach, but it has served me well.

-PatP

I guess it depends on your level of abstraction. If you define a business entity on a concrete level.... sure....

When you abstract department, as an 'organization' or 'group' of people, or something similar, you'll be hard pressed to find a natural key.

Vmusic