Q
Get started Bring yourself up to speed with our introductory content.

What are primary, super, foreign and candidate keys in a DBMS?

Here's a guide to primary, super, foreign and candidate keys, what they're used for in relational database management systems and the differences between them.

Database keys are essential components of a relational database management system. Keys are used to specify identifying...

attributes in the rows of database tables so the data can be sorted and organized for use in applications; they also create links between different tables to reduce data duplication while making it look like all of the data in a table is a single entity.

However, there are various types of database keys that serve different purposes in a database management system (DBMS), including primary, super, foreign and candidate keys. Let's look at each of these keys and the relationships and differences between them.

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.

CUSTOMERS

CustomerNo

FirstName

LastName

1

Sally

Thompson

2

Sally

Henderson

3

Harry

Henderson

4

Sandra

Wellington

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.

Commonly used database keys
Details on four commonly used database keys

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 keys in a DBMS, 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 DBMS.

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.

ORDERS

OrderNo

EmployeeNo

CustomerNo

Supplier

Price

Item

1

1

42

Harrison

$235

Desk

2

4

1

Ford

$234

Chair

3

1

68

Harrison

$415

Table

4

2

112

Ford

$350

Lamp

5

3

42

Ford

$234

Chair

6

2

112

Ford

$350

Lamp

7

2

42

Harrison

$235

Desk

If we want to be really 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:

EMPLOYEES

SSecurityNo

EmployeeNo

FirstName

LastName

DateOfBirth

DateEmployed

AF-23432334

1

Manny

Tomanny

12 Apr 1966

01 May 1999

DQ-65444444

2

Rosanne

Kolumns

21 Mar 1977

01 Jan 2000

GF-54354543

3

Cas

Kade

01 May 1977

01 Apr 2002

JK-34333432

4

Norma

Lyzation

03 Apr 1966

01 Apr 2002

VB-48565444

5

Juan

Tomani

12 Apr 1966

01 Apr 2002

FG-23566553

6

Del

Eats

01 May 1967

01 May 2004

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 real live production database. In practice, foreign keys almost always point to primary keys in a DBMS.

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

This was last published in June 2018

Dig Deeper on Database management system (DBMS) architecture, design and strategy

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

What kind of challenges do you face when choosing and managing database keys?
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close