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).
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.
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.
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