Ask the Expert

Definition of primary, super, foreign and candidate key in the DBMS

What is the primary key, super key, foreign key and candidate key in the DBMS?

    Requires Free Membership to View

A primary key is a column (or columns) in a table that uniquely identifies the rows in that table.

CUSTOMERS
 

CustomerNo FirstName LastName
1 Sally Thompson
2 Sally Henderson
3 Harry Henderson
4 Sandra Wellington

For example, in the table above, CustomerNo is the primary key.

The values placed in primary key columns must be unique for each row: no duplicates can be tolerated. In addition, nulls are not allowed in primary key columns.

So, having told you that it is possible to use one or more columns as a primary key, how do you decide which columns (and how many) to choose?

More on database management system (DBMS) technology

Read about how Facebook has raised awareness of graph database technology

Find out about the growth of cloud databases and the remaining obstacles

Learn how Pella implemented their database virtualization software initiative

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.

Next question — which column should you pick? The easiest way to choose a column as a primary key (and a method that is reasonably commonly employed) is to get the database itself to automatically allocate a unique number to each row.

In a table of employees, clearly any column like FirstName is a poor choice since you cannot control employee's first names. Often there is only one choice for the primary key, as in the case above. However, if there is more than one, these can be described as 'candidate keys' — the name reflects that they are candidates for the responsible job of primary 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, EmployeeNo AND FirstName as the primary key. That fulfils the requirement but it is clearly foolish because we are adding complexity for no gain. It is also a great example of a super key. Super, in this case, is not a synonym for 'great' but a contraction of supernumerary. Super keys are to be avoided.

Foreign keys are columns that point to primary key columns. So, for example, OrderNo is the primary key of the table ORDERS 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 at which a foreign key points is that it must contains unique values. Imagine, for example, that our employee tables 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 issue each employee with a number which is also unique. Under these circumstances, ORDERS.EmployeeNo can be a foreign key pointing to EMPLOYEES.EmployeeNo; even though that column is not a primary key. So the actual rule is slightly more subtle. A foreign key must point to a candidate 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.

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 are almost invariably pointed at primary keys.

Most of this answer was shamelessly plagiarized from Inside Relational Databases, a book by by Mark Whitehorn and Bill Marklyn, published by Springer Verlag. However, since Mark is one of the authors of the book, the chances of him suing himself are low.

This was first published in October 2009

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: