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.

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
2
  • 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
    Commented Apr 21, 2016 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
    Commented Apr 21, 2016 at 12:37
0

A surrogate key is a primary key of a record typically for a data warehouse, which represents a version of a record in the operational database. Data warehousing helps maintain the history of data overtime.

The operational database record might look like this. After the update, the old phone number is lost. The primary key is id.

id   name   phone
1    bob    555-555-5555 <---- record on day 1
1    bob    555-555-5556 <---- record on day 2; phone correction

The data warehouse record might look like this. The surrogate key is sid. The id here is often called a natural key, because we are making versions of it. After the phone number correction, the history is maintained, assuming the update didn't happen on the same day. Often times, a data warehouse will omit changes made on the same day, and only extracts what is current at the end of each day.

sid   id   name   phone
123   1    bob    555-555-5555
124   1    bob    555-555-5556

A composite key is a primary key made up of two or more columns.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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