9.1.8     Keys

This is NOT the latest copy of this book; click here for the latest version.

Some fields in tables are particularly important because they are used to define an individual record. Consider a table housing social security numbers - there should not be any duplicate numbers in there; each key is in there once and once only. In database terms we call this a primary key - you can select out one individual record from thousands by knowing its primary key.

A primary key when used as a field in another table other than its original table, is known as a foreign key. For example, in a WWW messageboard, each message must have the ID number of the person who posted a message (almost certainly the primary key in a members table), as well as the ID number of the messageboard it was posted on (almost certainly the primary key in a messageboards table). In this situation, both member ID and messageboard ID are primary keys in their own tables, but foreign keys in the messages table, because the messages primary key is the unique number of each message.

If that confused you, here's an example layout creating using Microsoft Access:

Above you can see the three tables, members, messageboards, and messages. Each has a primary key, ID, highlighted in bold. Note that MemberID and MessageBoardID exist in the messages table - these are the ID from the members table and the ID from the messageboards table respectively. Here is a picture of how that looks when linked up:

Now you can see clearly that messages has two foreign keys - one from members, and one from messageboards. Strictly speaking a foreign key is only a foreign key when defined as such, as there are special considerations to be taken into account when the primary key row is deleted from the master table - should the foreign key rows be deleted also? Having said that, if you just want to reference a primary key from another table, it works just as well in most situations.

MySQL supports primary keys completely, but not foreign keys fully as of yet. When foreign keys are supported across the board (version 5.1), you should be able to take advantage of referential integrity, which is where you can delete a record from a table, and any table which references it with a foreign key will also be updated. Primary keys are usually, but not always, unique - that is, you cannot have two values the same in a primary key column.