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
- the field used to tie two tables together
(the link field) is a primary key field in the parent table
and a foreign key in the child table,
- the link fields have an identical data type, and
- the two tables are in the same database.
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