Microsoft Office Tutorials and References
In Depth Information
Creating Relationships and Protecting Your Data with Referential Integrity
To see how you convert a one-to-many relationship to garbage, consider the
following scenario. Suppose that a table named Products contains a primary
key field named ProductID that uniquely identifies each record, and say
that a hammer in the Products table has a ProductID value of 232.
The Order Details table in that same database also has a field named
ProductID, which is the foreign key. Suppose that 100 hammers have been
ordered to date, and 100 records in the Order Details table have the value
232 in their ProductID fields.
Now someone comes along and decides to change the hammer’s ProductID
code to 98765. Or instead of changing the hammer’s ProductID, that
person just deletes that product from the Products table. Either way, a
record in the Products table no longer has a ProductID value of 232.
So what becomes of the 100 records in the Order Details table that still have
232 in their ProductID fields? Do you leave them referring to the
nownonexistent record 232? If you do that, you destroy the referential integrity
of the relationship between the tables.
How, you may ask, did you manage to do that? Well, a bunch of records
in the Order Details table now point to absolutely nothing; there’s no way
to tell what products the customers bought. The referential relationship
between the Products and Order Details tables has lost its integrity.
Enforcing referential integrity prevents these bad things from happening.
When you enforce referential integrity, you prevent yourself from
accidentally messing up your relationships. (Well, okay, that doesn’t apply to your
personal relationships, even abstractly, but you get the point.)
Some rules exist to help you determine whether you can even choose to
enforce referential integrity. You can enforce referential integrity only when
all the following statements are true of the tables in the relationship:
✦ In the master table, the matching field is a primary key or a field with its
Indexed property set to Yes (No Duplicates).
✦ In the detail table, the foreign key is of the same data type as the
primary key, or if the primary key is an AutoNumber field, the foreign key
is a Number field with its Field Size property set to the Long
✦ Both tables are stored in the same Access database.
Deciding on the best path to take
Assuming that all the rules for enforcing referential integrity are met (see the
preceding section for a refresher), you’re ready to get started. Just keep in
mind that you can enforce either of two distinct types of referential integrity: