Microsoft Office Tutorials and References
In Depth Information
A table with a composite key
StateAbbrev CityName CityPopulation
What is the primary key for the City table? The values for the CityPopulation column
periodically change and are not guaranteed to be unique, so the CityPopulation column
cannot be the primary key. Because the values for each of the other two columns are not
unique, the StateAbbrev column alone cannot be the primary key and neither can the
CityName column (for example, there are two cities named Madison and two cities
named Portland). The primary key is the combination of the StateAbbrev and CityName
columns. Both columns together are needed to identify—uniquely and minimally—each
row in the City table. A multiple-column primary key is called a composite key or a
concatenated key .
The StateAbbrev column in the City table is also a foreign key. A foreign key is a col-
umn, or a collection of columns, in one table in which each column value must match
the value of the primary key of some table or must be null. A null is the absence of a
value in a particular table entry. A null value is not blank, nor zero, nor any other value.
You give a null value to a column value when you do not know its value or when a value
does not apply. As shown in Figure A-4, the values in the City table’s StateAbbrev column
match the values in the State table’s StateAbbrev column. Thus, the StateAbbrev column,
the primary key of the State table, is a foreign key in the City table. Although the field
name StateAbbrev is the same in both tables, the names could be different. However, all
experts use the same name for a field stored in two or more tables to broadcast clearly
that they store similar values.