Microsoft Office Tutorials and References
In Depth Information
Problem Solving: Avoiding Inconsistent Data
Because related data is stored in two tables, inconsistencies between the tables can occur.
Referring to Figure 2-38, consider the following three scenarios:
• Oren adds a record to the Contract table for a new customer, Taylor McNulty, using
CustomerID 12050. Oren did not first add the new customer’s information to the
Customer table, so this contract does not have a matching record in the Customer table.
The data is inconsistent, and the contract record is considered to be an orphaned record .
• In another situation, Oren changes the CustomerID in the Customer table for Kalamazoo
Neighborhood Development from 11045 to 12090. Because there is no longer a customer
with the CustomerID 11045 in the Customer table, this change creates two orphaned
records in the Contract table, and the database is inconsistent.
• In a third scenario, Oren deletes the record for Kalamazoo Neighborhood Development,
customer 11045, from the Customer table because this customer no longer does business
with Belmont Landscapes. The database is again inconsistent; two records for customer
11045 in the Contract table have no matching record in the Customer table.
You can avoid these types of problems and avoid having inconsistent data in your
database by specifying referential integrity (discussed next) between tables when you define
Referential integrity is a set of rules that Access enforces to maintain consistency
between related tables when you update data in a database. Speciﬁ cally, the referential
integrity rules are as follows:
• When you add a record to a related table, a matching record must already exist in the
primary table, thereby preventing the possibility of orphaned records.
• If you attempt to change the value of the primary key in the primary table, Access
prevents this change if matching records exist in a related table. However, if you
choose the Cascade Update Related Fields option , Access permits the change in value
to the primary key and changes the appropriate foreign key values in the related table,
thereby eliminating the possibility of inconsistent data.
• When you attempt to delete a record in the primary table, Access prevents the
deletion if matching records exist in a related table. However, if you choose the Cascade
Delete Related Records option , Access deletes the record in the primary table and also
deletes all records in related tables that have matching foreign key values.
Understanding the Cascade Delete Related Records Option
Although there are advantages to using the Cascade Delete Related Records option for
enforcing referential integrity, its use does present risks as well. You should rarely select
the Cascade Delete Related Records option because setting this option might cause you
to inadvertently delete records you did not intend to delete. It is best to use other
methods for deleting records that give you more control over the deletion process.
Dei ning a Relationship Between Two Tables
When two tables have a common ﬁ eld, you can deﬁ ne a relationship between them
in the Relationships window (see the Session 2.2 Visual Overview). Next, you need
to deﬁ ne a one-to-many relationship between the Customer and Contract tables, with