2

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.

tsp216
  • 165
  • 1
  • 1
  • 4

1 Answers1

6

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.

Thomas Pullen
  • 472
  • 2
  • 7
  • 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? – tsp216 Apr 21 '16 at 12:11
  • 1
    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