Microsoft Access 2010 Bible

(Rick Simeone) #1

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

Free download pdf