DBMS keys: Primary, super, foreign and candidate keys with examples

Types of keys in a DBMS

There are various types of database keys that serve different purposes in a DBMS, including primary, super, foreign and candidate keys. Let's look at each of these keys and the relationships and differences among them.

The main types of keys in a DBMS are the following:

• primary key
• candidate key
• super key
• foreign key
• alternate key
• surrogate key
• composite key
• compound key

Below is a description of each of these DBMS keys.

Primary key

A primary key is a column -- or a group of columns -- in a table that uniquely identifies the rows in that table. For example, in the table below, CustomerNo, which displays the ID number assigned to different customers, is the primary key.

The data values placed in the primary key column must be unique to each row; no duplicates can be used. In addition, nulls are not allowed in primary key columns.

It is possible to use one or more columns as the primary key; however, how do you decide which columns -- and how many -- to choose?

Well, there are times when it is advisable or essential to use multiple columns. However, if you cannot see an immediate reason to use multiple columns, then use one. This isn't an absolute rule; it is simply advice.

However, primary keys made up of single columns are generally easier to maintain and faster in operation. This means that, if you query the database, you will usually get the answer back faster if the tables have single-column primary keys.

The next question you should ask is which column you should pick. The easiest way to choose a column to be a primary key -- and a method that is commonly employed -- is to get the database to automatically allocate a unique number to each row.

Candidate key

Often, there is only one choice for the primary key, as in the case above. However, if there are multiple DBMS keys, these can be called candidate keys -- the name reflects that they are candidates for the responsible job of the primary key.

A candidate key is a column that meets all of the requirements of a primary key. In other words, it has the potential to be a primary key, like the CustomerNo column. On the other hand, in a table of customers or employees, clearly, a column like FirstName is a poor candidate to be a primary key because you cannot control people's first names.

Super key

Now, given the definition above that a primary key can be made up of more than one column and must uniquely identify the rows, we could choose, for example, CustomerNo and a column containing customer phone numbers as the primary key. That fulfills the requirement, but it is clearly foolish because we are adding complexity for no reason.

It is also a great example of a super key with multiple unique identifiers. However, super, in this case, is not a synonym for great, but a contraction of supernumerary. My advice is to avoid super keys in DBMSes.

Foreign key

Foreign keys are columns that point to primary key columns in other database tables. So, for example, OrderNo is the primary key of the ORDERS table below, and CustomerNo is a foreign key that points to the primary key in the CUSTOMERS table.

If we want to be extremely pedantic, foreign keys don't have to point to a primary key. The only true requirement of the column to which a foreign key points is that it must contain unique values. Imagine, for example, that our employee table looked like this:

The Social Security number is actually the primary key of the table, but we also issue each person an employee number that is unique. Under these circumstances, ORDERS.EmployeeNo can be a foreign key pointing to EMPLOYEES.EmployeeNo, even though the latter column is not a primary key. So, the actual rule is slightly more subtle: A foreign key must point to a candidate key.

Having told you this, I can also tell you that I cannot remember the last time I saw this done in a live production database. In practice, foreign keys almost always point to primary keys in a DBMS.

Alternate key

An alternate key is essentially all the keys in a data table that are not the primary key. Remember that only one key can be set as the primary key. Once the primary key is established, all others are now considered alternate keys.

Surrogate key

A surrogate key is an artificial key that is created strictly for the purposes of data analysis. It's sometimes also called a synthetic key or a pseudokey because it isn't derived from any application data. Instead, it is used to identify objects in the database.

Composite key

A composite key is a key that has more than one attribute. Any super key, primary key or candidate key can be a composite key as long as it meets the requirement of having more than one attribute. By combining two or more columns in a table into a key, it guarantees that the key refers to a specific row. This is the primary use of composite keys.

Compound key

A compound key is a particular type of composite key in which each attribute is a foreign key.

Most of this information is from Inside Relational Databases, a book Mark Whitehorn co-authored with Bill Marklyn, published by Springer.