The fact that the column can contain Nulls suggests that it is probably not
suitable as the primary key by virtue of what 'attribute' it represents. The
data for a key must always be known and must be a value (Null is, despite the
message you got, not a value, but the absence of a value; though confusingly
the Value property of a column or control can be Null). Keys can be
'natural' or 'surrogate'. The data type is immaterial, so can be text,
number or date.
A 'natural' key is one made up of a column or columns which represent
meaningful data; e.g. a column of US states or UK counties could be the key
of a States or Counties table as in each case the names are distinct. A
column City in a table Cities is unsuitable as a primary key, however, as
city names are not infrequently duplicated.
A 'surrogate' key is one made up of a column of distinct values which are
arbitrary and serve only as a unique identifier. An 'autonumber' column of
the type Gina suggests is a surrogate key. An autonumber CityID column would
be a suitable key of a Cities table for instance.
Some people invariably use surrogate keys, usually by means of an autonumber
column, and often this has advantages. In some situations, though, a natural
key, where possible, can itself have advantages. One is where data is
entered or retrieved by the use of 'correlated combo boxes' where you select
a value from a list in one combo box which then limits the values in a list
in another combo box and so on. Where surrogate keys are used this does not
work well in a continuous form, as it will cause the values in the same
control in other rows to go blank when a different value is selected in the
current row; the values are still there in the column, but you don't see them.
There are ways around this, but they can be a bit kludgy, particularly if
more then two combo boxes are involved. Where a natural key is used, however,
the problem doesn't arise in the first place.
Another advantage of a natural key is that it can make it unnecessary to
include an additional table in a query. If you have a table of cities with a
State foreign key column referencing the natural key of a States table, say,
the States table does not have to be included in a query to see the state
name, as it would if a numeric surrogate StateID key were used. This does
not mean that the Sates table is redundant, however. It is necessary to
enforce referential integrity so that only valid state names can be entered
in Cities. Also, without it, if you don't have rows in cities for at least
one city in each state, then some states will be missing from the database
completely. You might say so what, we all know what they are, but a good
database should always cater for the Martian visitor with no prior knowledge.
Bear in mind that any table will always have a 'natural' candidate key, even
if it is a composite one made up of all columns in the table. If,
hypothetically, there were two cities of the same name in the same state you
might think that there can be no natural key as the row for each would have
the same values in each column. That is true, but the table would not be a
valid one as there is no way of knowing which is which. There would have to
be another column of real data which distinguishes the two, and it would be
the combination of all three columns which is the candidate key. Merely
introducing an autonumber column as the key is insufficient.
Finally, if a surrogate key is used, and there can be one or more other
candidate keys for the table, e.g. if a StateID was used as the key of States,
then its essential that the other candidate keys, whether made up of one or
more columns, are indexed uniquely to prevent duplicate values being
invalidly inserted.
So, in your case you need either to identify a column or set of columns whose
values uniquely identify each row in the table and use that column or columns
as the key, or introduce a surrogate key, e.g. by means of an autonumber
column.
Ken Sheridan
Stafford, England
