DBMS Relational keys

Key plays an important role in relational database; it is used for identifying unique rows from table. It also establishes relationship among tables.

Super Key

A super key is a set of one or more attributes (columns), which can uniquely identify a row in a table.

Example

Super keys: The above table has following super keys. All of the following sets of super key are able to uniquely identify a row of the employee table.

{Emp_SSN}

{Emp_Number}

{Emp_SSN, Emp_Number}

{Emp_SSN, Emp_Name}

{Emp_SSN, Emp_Number, Emp_Name}

{Emp_Number, Emp_Name}

Candidate Key

A candidate key is a minimal super key with no redundant attributes. The following two set of super keys are chosen from the above sets as there are no redundant attributes in these sets.

{Emp_SSN}

{Emp_Number}

Only these two sets are candidate keys as all other sets are having redundant attributes that are not necessary for unique identification.

Super key vs Candidate Key

All the candidate keys are super keys. This is because the candidate keys are chosen out of the super keys.

How we choose candidate keys from the set of super keys? We look for those keys from which we cannot remove any fields. In the above example, we have not chosen {Emp_SSN, Emp_Name} as candidate key because {Emp_SSN} alone can identify a unique row in the table and Emp_Name is redundant.

Primary key

A Primary key is selected from a set of candidate keys. This is done by database admin or database designer. We can say that either {Emp_SSN} or {Emp_Number} can be chosen as a primary key for the table Employee.

Foreign key

Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.Foreign keys are the columns of a table that points to the candidate key of another table.

Composite key

A key that has more than one attributes is known as composite key. It is also known as compound key. Any key such as super key, primary key, candidate key etc. can be called composite key if it has more than one attributes.

Example Composite key

None of these columns alone can play a role of key in this table.

Column cust_Id alone cannot become a key as a same customer can place multiple orders, thus the same customer can have multiple entires

Column order_Id alone cannot be a primary key as a same order can contain the order of multiple products, thus same order_Id can be present multiple times.

Column product_code cannot be a primary key as more than one customers can place order for the same product.

Column product_count alone cannot be a primary key because two orders can be placed for the same product count.

Based on this, it is safe to assume that the key should be having more than one attributes:

Key in above table: {cust_id, product_code}

This is a composite key as it is made up of more than one attributes

Alternate key

As we have seen in the candidate key guide that a table can have multiple candidate keys. Among these candidate keys, only one key gets selected as primary key, the remaining keys are known as alternative or secondary keys.