5.1: Integrity

Primary Key

Recall that a table’s PK is a field (possibly composite) that has unique values – each row has a PK value different from any other row in the table. Such a field is a unique identifier

• • • if a query were designed to retrieve a row of that table based on a value of the PK, then at most one row of the table will be retrieved.

Foreign Key

A foreign key is a field (or combination of fields) in a table B that is associated with a PK in a table A through a relationship (A and B can be the same table).

Entity Integrity

When we define a PK for a table, we are enforcing entity integrity. Entity integrity means that each row in the table is identifiable through its primary key. MS Access requires a value for a PK in a newly added row, and MS Access enforces uniqueness of those values.

Referential Integrity

Suppose we have two tables, A and B, where a relationship is defined between the primary key of table A and a foreign key in table B. We say referential integrity (RI) exists for this relationship if for each row in B either:

• • • the FK has no value at all (i.e., it is null), or
    • the FK has a value that exists as a PK value in some row of A.