LIS 558 - Referential integrity

Referential integrity is a mechanism that enforces the ties between data in separate tables and prevents them from being broken.

Referential integrity minimizes the undesirable likelihood of the existence of a record in the child table for which there is no corresponding record in the parent table (referred to as an orphan, or dangling, record).

Before setting referential integrity, ensure that

Default referential integrity in Access

A value (other than null) cannot be entered in the foreign key field of the related table (the one on the right hand side in the "Edit Relationships" dialog) if that value does not exist in the primary key of the parent table (the one on the left hand side in the dialog). A null value specifies that the record in the child table is not linked to any record in the parent table.

A record cannot be deleted from a parent table if matching records exist in a child table.

A primary key value in the parent table cannot be changed, if that record has related records.

Options

Before selecting one of the following options, make sure that you understand the implications.

Cascade update option

When a primary key value is changed in the parent table, the same change is made automatically to the foreign key value(s) in the child table so that the relationship is maintained.

Cascade delete option

When a record is deleted in the primary table, all related records in the child table are automatically deleted so that there will be no orphan records.
Home

Last updated July 5, 2001.
This page maintained by Prof. Tim Craven
E-mail (text/plain only): craven@uwo.ca
Faculty of Information and Media Studies
University of Western Ontario,
London, Ontario
Canada, N6A 5B7