Super key, Candidate key and Primary key

A superkey is a set of one or more attributes that taken collectively allow us to identify uniquely an entity in the entity set. For example, the customer_id attribute of the entity set customer is sufficient to distinguish one customer entity from another. Thus, customer_id is a super key. Similarly, the combination of customer_id and customer_name is a superkey for the entity set customer. Thus, a minimal super key with extraneous attributes is also a superkey. However only customer_name attribute of customer is not a superkey, because several people might have the same name.

Thus, we now know that if K is a superkey, then so is any superset of K. But we are often interested in minimal superkeys with no extraneous attributes. Such minimal superkeys are called candidate keys.

Several distinct sets of attributes could serve as a candidate key so long they identify uniquely an entity in the entity set. Consider the combination of customer_name and customer_street. This set of attributes can distinguish uniquely among members of the customer entity set. Therefore, both {customer_id} and {customer_name, customer_street} are candidate keys. But the latter is not a super key. Although the attributes customer_id and customer_name can distinguish customer entities, their combination does not form a candidate key, since the attribute customer_id alone is a candidate key. This set is, however, a super key.

We shall use the term primary key to denote a candidate key that is chosen by the database designer as the principal means of identifying entities within an entity set. Unlike super keys and candidate keys, there can always only be one way of representing a primary key for the entities of the entity set. It is noteworthy that every entity set must have a primary key to distinguish uniquely the entities within the entity set and to distinguish from other entity sets.