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