As you can see, establishing a relationship is quite
easy. Establishing the right kind of relationship is a little more
difficult. When you attempt to establish a relationship between two
tables, Access makes some decisions based on a few predefined factors:
Access establishes a one-to-many relationship if one of the related fields is a primary key or has a unique index.
Access establishes a one-to-one relationship if both of the related fields are primary keys or have unique indexes.
Access
creates an indeterminate relationship if neither of the related fields
is a primary key and neither has a unique index. You cannot establish
referential integrity in this case.
Referential integrity
consists of a series of rules that Access applies to ensure that it
properly maintains the relationships between tables. At the most basic
level, referential integrity rules prevent the creation of orphan
records in the table on the “many” side of the one-to-many
relationship. After you establish a relationship between a Customers
table and an Orders table, for example, all orders in the Orders table
must be related to a particular customer in the Customers table. Before
you can establish referential integrity between two tables, the
following conditions must be met:
The matching field on the “one” side of the relationship must be a primary key field or must have a unique index.
The
matching fields must have the same data types. (For linking purposes,
AutoNumber fields match Long Integer fields.) With the exception of
Text fields, the matching fields also must have the same size. Number
fields on both sides of the relationship must have the same size (for
example, Long Integer).
Both tables must be part of the same Access database.
Both
tables must be stored in one of the proprietary Access file (MDB or
ACCDB) formats. (They cannot be external tables from other sources.)
The database that contains the two tables must be open.
Existing
data within the two tables cannot violate any referential integrity
rules. All orders in the Orders table must relate to existing customers
in the Customers table, for example.
|
Although Text fields involved in a relationship do
not have to be the same size, it is prudent to make them the same size.
Otherwise, you degrade performance and risk the chance of unpredictable
results when you create queries based on the two tables.
|
After you establish referential integrity between two tables, Access applies the following rules:
You cannot enter in the foreign key of the
related table a value that does not exist in the primary key of the
primary table. For example, you cannot enter in the CustomerID field of
the Orders table a value that does not exist in the CustomerID field of
the Customers table.
You cannot delete a
record from the primary table if corresponding records exist in the
related table. For example, you cannot delete a customer from the
Customers table if related records (for example, records with the same
value in the CustomerID field) exist in the Orders table.
You
cannot change the value of a primary key on the “one” side of a
relationship if corresponding records exist in the related table. For
example, you cannot change the value in the CustomerID field of the
Customers table if corresponding orders exist in the Orders table.
If you attempt to violate any of these three rules
and you have enforced referential integrity between the tables, Access
displays an appropriate error message, as shown in Figure 1.
When you establish referential integrity in Access,
its default behavior is to prohibit the deletion of parent records that
have associated child records and to prohibit the change of a primary
key value of a parent record when that parent has associated child
records. You can override these restrictions by using the Cascade
Update Related Fields and Cascade Delete Related Records check boxes
that are available in the Relationships dialog box when you establish
or modify a relationship.
The Cascade Update Related Fields Option
The Cascade Update Related Fields option is
available only if you have established referential integrity between
tables. When this option is selected, the user can change the primary
key value of the record on the “one” side of the relationship. When the
user attempts to modify the field joining the two tables on the “one”
side of the relationship, Access cascades the change down to the
foreign key field on the “many” side of the relationship. This is
useful if the primary key field is modifiable. For example, a purchase
number on a purchase order master record might be updateable. If the
user modifies the purchase order number of the parent record, you would
want to cascade the change to the associated detail records in the
purchase order detail table.
It
is easy to accidentally introduce a loophole into a system. If you
create a one-to-many relationship between two tables but forget to set
the Required property of the foreign key field to Yes, you allow the addition of orphan records. Figure 2
illustrates this point. In this example, I added an order to tblOrders
without entering a customer ID. This record is an orphan record because
no records in tblCustomers have CustomerID set to Null. To eliminate the problem, you set the Required property of the foreign key field to Yes.
|
There is no need to select the Cascade Update
Related Fields option when the related field on the “one” side of the
relationship is an AutoNumber field. You can never modify an AutoNumber
field. The Cascade Update Related Fields option has no effect on
AutoNumber fields. In fact, this is why, in the preceding Task, you
make the CustomerID a Text field. It provides an example that you can
later use with a cascade update.
|
The Cascade Delete Related Records Option
The Cascade Delete Related Records option is
available only if you have established referential integrity between
tables. When this option is selected, the user can delete a record on
the “one” side of a one-to-many relationship, even if related records
exist in the table on the “many” side of the relationship. A user can
delete a customer even if the customer has existing orders, for
example. The Jet Engine maintains referential integrity between the
tables because it automatically deletes all related records in the
child table.
If you attempt to delete a record from the table on
the “one” side of a one-to-many relationship and no related records
exist in the table on the “many” side of the relationship, you get the
usual warning message, as shown in Figure 3.
On the other hand, if you attempt to delete a record from the table on
the “one” side of a one-to-many relationship and related records exist
in the child table, Access warns you that you are about to delete the
record from the parent table and any related records in the child table
(see Figure 4).
The Cascade Delete Related Records option is not
always appropriate. It is an excellent feature, but you should use it
prudently. Although it is usually appropriate to cascade delete from an
Orders table to an Order Details table, for example, it generally is
not appropriate to cascade delete from a Customers table to an Orders
table. This is because you generally do not want to delete all your
order history from the Orders table if for some reason you want to
delete a customer. Deleting the order history causes important
information, such as the profit and loss history, to change. It is
therefore appropriate to prohibit this type of deletion and handle the
customer in some other way, such as marking him or her as inactive or
archiving his or her data. On the other hand, if you delete an order
because the customer cancelled it, you probably want to remove the
corresponding order detail information, too. In this case, the Cascade
Delete Related Records option is appropriate. You need to make the most
prudent decision in each situation, based on business needs. You need
to carefully consider the implications of each option before you make a
decision.
