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:
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.
