A surrogate key without an alternate key, can it be?

A surrogate key without an alternate key, can it be?

A surrogate key is a unique identifier for a table, often a simple counter created by developers or database administrators. In my classes, I strongly recommend tagging what the “real” primary key is in the table – that is, the one that a business professional would consider the unique identifier. We tag by using the concept of an alternate key. For example, if Employee Identifier is the surrogate key of the Employee table, then an alternate key could be Employee Social Security Number. Order Line may have Order Line Identifier as its surrogate key, and both Product Code and Order Number as its alternate key.

There are times however, when a surrogate key may not have a corresponding alternate key, such as when there is no real primary key. That is, there is no other way of identifying records in this table other than through the use of a surrogate key. For example, I worked on a data warehouse project where we had no way of identifying a single record for a certain type of transaction, and therefore we created a surrogate key on this table without having an alternate key.

Are there other situations where we may not have or need an alternate key when using a surrogate key? Explain your answer.

Read the response to this challenge


Leave a reply

Your email address will not be published. Required fields are marked *