Consider the sample Northwind database that includes information about customers, orders, products, shippers, and other related items ( Figure 1 ). Integrity refers to rules that maintain the consistency of specific relationships, and thus maintain the consistency of data stored in the related tables. Two types of rules govern integrity in a relational database: Entity integrity dictates that each row be uniquely identified (which implies that no primary key value can equal a Null value). Referential integrity specifies that a foreign key value must match a primary key value in a related table or be Null.
Applications must enforce referential integrity, so that users can't:
Note that the terms primary key value and foreign key value refer here to an entire record, not just the value in the key field.
In Access, the best place to learn about referential integrity is the Relationships window, which displays a graphical representation of the relationships between tables. A relationship must exist between two tables in order to apply referential integrity constraints. The Relationships window won't display temporary relationships that you create manually in the query design window. Rather, the relationships displayed are permanent and affect all objects.
To get an idea of how the Relationships windows works, open the sample Northwind database in Access and Choose Tools | Relationships.... Note the relationships between the many Northwind tables, as shown in Figure 1. Each join line represents a relationship between two tables. A one-to-many relationship is the defaultand the most commonwith an infinity symbol at the many side and a 1 at the one side. A join line without these symbols represents a relationship for which referential integrity is not turned on.