Database Reference
In-Depth Information
find that the foreign key is already part of the table's structure. If the foreign key does not
already exist, you'll simply create one.
Let's revisit the MEMBERS table example from
Figure 10.20
.
Recall that this table has a
self-referencingone-to-onerelationshipbecauseagivenmembercansponsoronlyoneoth-
er member within the organization; the S
PONSOR
ID field stores the member identification
number of the member acting as a sponsor. Because the S
PONSOR
ID field draws its values
exclusively from the M
EMBER
ID field, it acts as the foreign key for the relationship. You
establish the relationship by officially designating the S
PONSOR
ID field as the foreign key
andnotatingitassuchintherelationshipdiagram.
Figure10.45
showstherevisedrelation-
ship diagram for the MEMBERS table.
Figure 10.45. Establishing the self-referencing one-to-one relationship for the
MEMBERS table
Now,considertheSTAFFtableexamplein
Figure10.46
.Youmayrememberthatthistable
has a self-referencing one-to-many relationship because a single staff member can manage
one or more other staff members.
