0

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.

3

A composite key is simply a key that contains two or more columns.

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? – user98937 Apr 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 Pullen Apr 21 '16 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.