Database Reference
In-Depth Information
Note
I encounter this issue quite often when I'm asked to analyze certain types of data-
base problems. In many cases, the foreign keys are either completely inappropri-
ate or manifest serious data-integrity and relationship-integrity problems. Once I
identify the appropriate foreign keys (or revise the existing ones) and ensure that
they comply with this particular element, a number of problems disappear.
The only time I can justify and approve of using a different name for the foreign
key field is when I establish a
self-referencing
relationship for a given table. This
is reasonable because the primary key and foreign key both reside within the table
(in most cases), and each must have a unique name.
•
It uses a replica of the field specifications for the primary key from which it was
copied.
This supports the sixth element of an ideal field, which you learned in
Chapter 7
(“It retains a majority of its properties when it appears in more than one
table”). A foreign key, however, has a few settings in both the General Elements
and Logical Elements categories that are slightly different from those of its parent
primary key.
There are four elements in the General Elements category that you will modify
when you define a field specification for a foreign key.
a.
Specification Type:
Because a foreign key is based on an existing primary key,
it inherits a
replica
of the primary key's field specifications; therefore, you
designate the foreign key's specification type as “Replica.” This designation
helps you ensure that your foreign key specifications are consistent, and re-
minds you to keep this specification synchronized with the primary key's spe-
cification.
b.
Parent Table
:
The name of the
foreign key's
parent table goes here.
c.
Source Specification:
This is where you indicate the name of the parent
primary key. (Make certain you include the name of the primary key's parent
table as well; this will make it easier for you to find the primary key's specific-
ation should you want to compare it to the foreign key's specification.)
d.
Description:
Compose a description that indicates the foreign key's purpose
within the table.
Figure 10.53
shows an example of these modifications for an
E
MPLOYEE
ID N
UMBER
field serving as a foreign key in an ORDERS table.
