Database Reference
In-Depth Information
There is also another issue with the relational model. Nothing about the model itself would prevent a user
from storing data about a customer whose sales rep number did not correspond to any sales rep already in the
database. Clearly, this is not a desirable situation.
Fortunately, a solution exists for both issues. It involves using foreign keys.
Definition:
A foreign key is a field (or collection of fields) in a table whose value is required to match the
value of the primary key for a second table.
The RepNum field in the Customer table is a foreign key that must match the primary key of the Rep
table. In practice, this means that the sales rep number for any customer must be the same as the number of
a sales rep that is already in the database.
There is one possible exception to this rule. Perhaps Premiere Products does not require a customer to
have a sales rep
133
—
it is strictly optional. This situation could be indicated in the Customer table by setting
such a customer
s sales rep number to null. Technically, however, a null sales rep number would violate the
restrictions that you have indicated for a foreign key. Thus, if you were to use a null sales rep number, you
would have to modify the definition of a foreign key to include the possibility of nulls. You would insist,
though, that if the foreign key contained a value other than null, it would have to match the value of the pri-
mary key in some row in the other table. (In the example, for instance, a customer
'
s sales rep number could
be null. If it were not null, it would have to be the number of an actual sales rep.) This general property is
called referential integrity.
'
Definition:
Referential integrity is the rule that if table A contains a foreign key that matches the primary
key of table B, the values of this foreign key must match the value of the primary key for some row in table B
or be null.
Usually a foreign key is in a table that is different from the primary key it is required to match. In the
Premiere Products database, for example, to be able to determine the rep for any customer, you include the
rep number as a foreign key in the Customer table that must match the primary key in the Rep table. It is
possible for the foreign key and the matching primary key to be in the same table, however. As an example of
this situation, suppose one of the requirements in a particular database is that, given an employee, you must
be able to determine the manager of that employee. You might have an Employee table with a primary key of
EmployeeNum (the employee number). To determine the employee
'
s manager, you would include the man-
ager
s employee number as a foreign key in the Employee table. Because the manager is also an employee,
however, the manager will be in the same Employee table. Thus, this foreign key in the Employee table would
need to match the primary key in the same Employee table. The only restriction is that the foreign key must
have a name that is different from the primary key because the fields are in the same table. For example, you
could name the foreign key ManagerEmployeeNum.
Using foreign keys solves the previously mentioned problems. Indicating that the RepNum field in the
Customer table is a foreign key that must match the RepNum field in the Rep table explicitly specifies the
relationship between customers and sales reps
'
you do not need to look for common fields in several tables.
Further, with foreign keys, matching fields that have different names no longer pose a problem. For example,
it would not matter if the name of the foreign key in the Customer table were SlsrNo and the primary key in
the Rep table were RepNum; the only thing that would matter is that this field is a foreign key that matches
the Rep table. Finally, through referential integrity, it is possible for a customer not to have a sales rep num-
ber, but it is not possible for a customer to have an invalid sales rep number; that is, a customer
—
'
s sales rep
number must be null or must be the number of a sales rep who is already in the database.
In SQL, you specify referential integrity using a FOREIGN KEY clause in either the CREATE TABLE or
ALTER TABLE commands. To specify a foreign key, you need to specify both the field that is a foreign key
and the table whose primary key it is to match. In the Customer table, for example, the RepNum field is a
foreign key that must match the primary key in the Rep table as follows:
FOREIGN KEY (RepNum) REFERENCES Rep
The general form of this clause is FOREIGN KEY, followed by the field or combination of fields that make
up the foreign key, which is followed by the word REFERENCES and the name of the table containing the pri-
mary key that the foreign key is supposed to match.
In Access, referential integrity is specified as part of the process of defining relationships, as shown in
Figure 4-17.
