Table Keys

Each database table is consists of rows and columns. Some columns are special columns because they identify a particular record in a table. The special columns are called keys. The 2 most common keys are primary keys and foreign keys.

In a database table, the column that uniquely identifies each record in the table is called a primary key. It is almost always good design to define a primary key for all tables. In some cases, it is not possible to identify a column to be used as the primary key. In such cases, most designers create a dummy column which simply contains a running number to make each and every record in the table unique - for example, the AutoNumber data type in a Microsoft Access database.

Primary keys are also sometimes created from a combination of 2 or more columns. Such primary keys are called composite keys. Each column may not be unique by itself within the database table but when combined with the other column(s) in the composite key, the combination is unique.

When a database table is normalized to create an efficient and compact design, multiple tables are created that are linked together using foreign keys. Foreign keys are not necessarily unique in the table that stores them but they point to unique values in the referenced table.

To illustrate the concepts of the primary key and the foreign key, consider the sample table design below:

product table
product_id primary key
category_id foreign key

category table
category_id primary key

Let's assume that the above tables contain the following data:
0001 table 0001
0002 chair 0001
0003 fan 0002

0001 furniture
0002 electrical

Notice that in both the product table and the category table, the values in the product_id column and the category_id are never repeated. Both the product_id and the category_id columns are primary keys within their respective tables.

Also, notice that although the category_id column in the product table contains duplicate values, they point to records in the category table defined by unique category_id columns. The category_id column is a primary key in the category table but is a foreign key in the product table.

See also: Composite keys and Indexes