Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.

I'm a beginner with databases and have been perplexed by the various definitions to distinguish these two. Various google searches have not fully clarified their differences. Please help explain them on a simple level.

1 Answer
1

A surrogate key is one which is not naturally unique to the data but is added or imposed onto the data for (hopefully) a good reason. Examples can include IDENTITY columns in SQL Server - known as autonumbers sometimes in other products.

Perhaps you are storing information about people, but you can't be sure that they can be uniquely identified by a combination of their name, date of birth etc.. If you don't have some external information about them that ascribes uniqueness (e.g. Social Security Number), you can use a surrogate key to uniquely identify the rows.

So... a surrogate key is a single field whereas a composite key is two or more fields? Also, either key can be used as a primary key? Is it possible for either key to exist if one or both does not serve as a primary key?
– user98937Apr 21 '16 at 12:11

A surrogate key can also be composite, although often in practice it is just one column. Yes, "key" when referred to by both terms implies "primary key". Occasionally tables can have 2 different things that make rows unique, which can be implemented in SQL Server through having one as the primary key and the other as a unique index or unique constraint.
– Thomas PullenApr 21 '16 at 12:37