A primary key is a table column that can be used to uniquely identify every row of the table. Any column that has this property will do -- these columns are called candidate keys. A table can have many candidate keys but only one primary key. The primary key cannot be null.
Consider the following table --
FooNumber FirstName LastName BarTab 21 Fred Jones 47 32 Bill Smith 23 87 Wendy Jones - 32 Bob Stikino 943
In this example, only FirstName is a single-column candidate key, because it is the only column that is unique and not null.
A composite primary key is a primary key consisting of more than one column. In the above example, the combinations (RecordNo,FirstName), (RecordNo,Lastname), (RecordNo,FirstName,Lastname), and (FirstName,LastName) are all candidate keys. Any combination including Age is not a candidate key because it contains a null.
Often, database designers add an extra column to their table designs, a column defined as an integer, which will hold a number. In Microsoft Access, this is an autonumber, in MySQL it's an auto-increment, in Oracle it's a sequence, and in SQL/Server it's an identity column. As these names suggest, this integer is automatically assigned by the database, usually incrementally, sometimes using an initial value and increment that you can specify. Some databases allow these numbers to be generated randomly.
The purpose of this type of automatically generated number is to act as the surrogate primary key, usually in those situations similar to the above where candidate keys are multi-column. The awkwardness of a multi-column candidate key becomes apparent as soon as you define a foreign key on it.
A foreign key is a column, or combination of columns, that contain values that are found in the primary key of some table (including, possibly, itself). A foreign key may be null, and almost always is not unique.
That last statement may be counterintuitive, so let's take another example. Here we have two tables that are related via a foreign key --
ClrPK ColourName 10 yellow 20 red 30 green 40 blue
AdjPK AdjectiveName ClrFK 904 angry 20 913 envious 30 937 lazy - 941 lonely 40 954 fearful 10 979 jealous 30 991 furious 20
As you can see, the ClrFK column in the second table is a foreign key to the ClrPK primary key in the first table. Notice that the ClrPK values are unique and not null, but the ClrFK values may be null and often repeat. A null foreign key means that that particular row does not participate in the relationship. The fact that many foreign key values repeat simply reflects the fact that it's a one-to-many relationship.
In a one-to-many relationship, the primary key has the "one" value, and the foreign key has the "many" values. The trick to remembering this is to keep in mind that the primary key must be unique.
This was first published in February 2002