.:: SQL Server Natural and Surrogate Primary Keys ::.
I posted the following message to the microsoft.public.sqlserver.programming news group: What’s the best practice for primary keys? As expected, I received great responses from some respected and knowledgeable database architects. Here is what I’ve learned through links provided and responses to my question..
There are two different types of primary keys: Surrogate and Natural
Surrogate:
Defines a primary key that is assigned from the database system or another automatically generated number source and is unrelated to the actual data contained within the table. Some examples of surrogate primary keys are auto increment IDENTITY and GUID().
Natural:
A natural key is comprised of columns in a database that are part of the actual data. For instance, to combine the FirstName and LastName fields in a table as a primary key.
My Take:
Based on the research that I have done, I think that Surrogate keys make the most sense. Such a strategy relieves complications of deciding which natural keys to use and if the natural keys can be duplicated thus causing an error. Keeping these decisions simple allows for more focus on the business problem the application is trying to solve.