Microsoft Office Tutorials and References
In Depth Information
The property that ensures that the value in a foreign key must match that of another
table’s primary key is called referential integrity . A foreign key is a ﬁ eld in one table
whose values are required to match the primary key of another table. In the Client table,
the Recruiter Number ﬁ eld is a foreign key that must match the primary key of the
Recruiter table; that is, the Recruiter number for any client must be a recruiter currently
in the Recruiter table. A client whose Recruiter number is 92, for example, should not be
stored because no such recruiter exists.
In Access, to specify referential integrity, you must deﬁ ne a relationship between
the tables by using the Relationships command. Access then prohibits any updates to the
database that would violate the referential integrity.
The type of relationship between two tables speciﬁ ed by the Relationships command
is referred to as a one-to-many relationship . This means that one record in the ﬁ rst table
is related to (matches) many records in the second table, but each record in the second table
is related to only one record in the ﬁ rst. In the JSP Recruiters database, for example, a
one-to-many relationship exists between the Recruiter table and the Client table. One
recruiter is associated with many clients, but each client is associated with only a single
recruiter. In general, the table containing the foreign key will be the many part of the
Identify related tables in order to implement relationships between the tables.
When specifying referential integrity, you need to decide how to handle deletions. In the
relationship between clients and recruiters, for example, deletion of a recruiter for whom
clients exist, such as recruiter number 21, would violate referential integrity. Any clients for
recruiter 21 no longer would relate to any recruiter in the database. You can handle this in
two ways. For each relationship, you need to decide which of the approaches is appropriate.
1. The normal way to avoid this problem is to prohibit such a deletion.
2. The other option is to cascade the delete. This means that Access would allow the
deletion but then delete all related records. For example, it would allow the deletion of
the recruiter but then automatically delete any clients related to the deleted recruiter.
You also need to decide how to handle the update of the primary key. In the relationship
between recruiters and clients, for example, changing the recruiter number for recruiter 21 to
12 in the Recruiter table would cause a problem. Clients are in the Client table on which the
recruiter number is 21. These clients no longer would relate to any recruiter. You can handle
this in two ways. For each relationship, you need to decide which of the approaches is
1. The normal way of avoiding the problem is to prohibit this type of update.
2. The other option is to cascade the update . This means to allow the change, but make
the corresponding change in the foreign key on all related records. In the relationship
between clients and recruiters, for example, Access would allow the update but then
automatically make the corresponding change for any client whose recruiter number
was 21. It now will be 12.