Terminology - Database Design for Mere Mortals - page 44

Figure 3.11. An example of primary and foreign key fields
When you determine that two tables bear a relationship to each other, you typically estab-
lish the relationship by taking a copy of the primary key from the first table and incorpor-
ating it into the structure of the second table, where it becomes a foreign key. The name
“foreign key” is derived from the fact that the second table already has a primary key of its
own,andtheprimarykeyyouareintroducingfromthefirsttableis“foreign”tothesecond
table.
Figure 3.11 also shows a good example of a foreign key. Note that A GENT ID is the
primary key of the AGENTS table and a foreign key in the ENTERTAINERS table.
A GENT ID assumes this role because the ENTERTAINERS table already has a primary
key—E NTERTAINER ID.Assuch, A GENT IDestablishes the relationship between both ofthe
tables.
Besides helping to establish relationships between pairs of tables, foreign keys also help
implement and ensure relationship-level integrity. This means that the records in both
tableswillalwaysbeproperlyrelatedbecausethevaluesofaforeignkey must match exist-
ing values of the primary key to which it refers. Relationship-level integrity also helps you
avoidthedreaded“orphaned”record,aclassicexampleofwhichisanorderrecordwithout
an associated customer. If you don't know who made the order, you can't process it, and
you obviously can't invoice it. That'll throw your quarterly sales off!