Microsoft Access 2010 Bible

Part I: Access Building Blocks

112

Integrity Rules

Access permits you to apply referential integrity rules that protect data from loss or corruption.

Referential integrity means that the relationships between tables are preserved during updates, dele-

tions, and other record operations. The relational model defines several rules meant to enforce the

referential integrity requirements of relational databases. In addition, Access contains its own set of

referential integrity rules that are enforced by the Jet database engine.

Imagine a payroll application that contained no rules regulating how data in the database is used.

It’d be possible to issue payroll checks that aren’t linked to an employee, for instance. From a busi-

ness perspective, issuing paychecks to “phantom” employees is a very serious situation. Eventually,

the issue will be noticed when the auditors step in and notify management of the discrepancy.

Referential integrity operates strictly on the basis of the tables’ key fields. Referential integrity

means that the database engine checks each time a key field (whether primary or foreign) is added,

changed, or deleted. If a change to a value in a key field invalidates a relationship, it is said to vio-

late referential integrity. Tables can be set up so that referential integrity is automatically enforced.

Figure 3.13 illustrates one of several relationships in the Collectible Mini Cars database. The

Products table is related to the Sales Lines Item table through the ProductID field. The

ProductID field in the Products table is the primary key, while the ProductID field in the

Sales Line Items table is a foreign key. The relationship connects each product with a line

item on a sales invoice. In this relationship, the Products table is the parent table, while the

Sales Line Items table is the child table.

FIGURE 3.13

A typical database relationship