Relational Database Fundamentals - Design and Use of Relational Databases in Chemistry - page 15

2.9 Keys
The importance of using one column of a table as a key was introduced
in tables epa.compound and epa.logP discussed above. In these tables, the
column named cid was defined as a key column. The term key is used to
denote the column by which two or more tables are related to one another.
This key column allows otherwise complex tables to be split into two or
more tables.
2.9.1 Primary Keys
A primary key column in a table is also a unique column. There is often
one central table in a schema by which the other tables in a schema are
related to each other. In the EPA schema discussed here, epa.compound
is the central table and the cid column is the unique, primary key. The
purpose of the epa.compound table is to provide a central registry of com-
pounds that are of interest in the schema. The key column is typically
defined using a sequence to ensure uniqueness.
2.9.2 Foreign Keys
In the table epa.logP, the column named cid is also used. Its purpose is
to relate the logP values to the compounds in the epa.compound table to
which they refer. The cid column in epa.logP is clearly not unique since
the table may contain multiple values of logP for any one compound and
therefore uses the same cid value multiple times. Other tables analogous
to epa.logP would use a column named cid in an identical way. A key col-
umn such as this, which is not unique and which relates to a primary key
column in another table, is called a foreign key. The name of the foreign
key column need not be the same as the name of its related primary key.
The foreign key column is not defined by the sequence used to define the
primary key, although it necessarily shares values with the primary key.
The primary key column might be considered as “responsible” for the
unique values relating the tables to each other. The foreign key columns
are dependent upon the primary key.
There are many ways in which primary and foreign key columns
can be used. A foreign key might also be unique, for example, in a table
epa.casid that stores the Chemical Abstracts identifier. A table might con-
tain two foreign keys, each of which relates to a primary key in different
tables. A table might also contain both primary and foreign keys, each
relating to keys in different tables. Proper use of entity-relationship dia-
grams becomes increasingly important as the complexity of the database
schema and the table relationships grows.