Foreign Keys

A foreign key serves a critical purpose in a relationship. It is an attribute or set of attributes that identifies the parent record. In other words, the foreign key is the attribute that links the child occurrences to the parent entity occurrence through matching key values. The foreign key is artificially present in the child entity and is the primary key of the parent.

Chapter 2—Logical and Physical Database Design

For example, in the relationship VENDOR supplies ITEMS, VENDOR® is the primary key in the VENDORS entity. This attribute appears in ITEMS entity as a foreign key, linking each item instance back to a sp< cific vendor. Figure 2.3 shows the resulting relation between the two entities.

VENDORS

VENDOR ID VENDOR NAME

VENDORS

VENDOR ID VENDOR NAME

ITEM #

ITEM DESC

VENDOR ID

901

Disk

200 i

902

CD-ROM

200

903

Book

100

904

Disk

400

i t f

Figure 2.3 The VENDOR-ID attribute is the primary key in the VENDORS ert®

and Q foreign key in the ITEMS entity.

Figure 2.3 The VENDOR-ID attribute is the primary key in the VENDORS ert®

and Q foreign key in the ITEMS entity.

Tip Do not select alternate keys as a foreign key. Alternate keys allow null values, leading to linkage problems not present if the primary key is used. In addition, the use of alternate keys leads to unnecessary complication of the database structure because their use requires indirect referencing back to the primary key anyway.

Was this article helpful?

0 0

Post a comment