Microsoft Office Tutorials and References
In Depth Information
• Matt deletes the record for Boston Harbor Excursions, employer 10125, in the Employer
table because this employer is no longer an NSJI client. The database is again inconsis-
tent; one record for employer 10125 in the Position table has no matching record in the
You can avoid these problems by specifying referential integrity between tables when
you define their relationships.
Referential integrity is a set of rules that Access enforces to maintain consistency between
related tables when you update data in a database. Specifically, the referential integrity
rules are as follows:
• When you add a record to a related table, a matching record must already exist in the
primary table, thereby preventing the possibility of orphaned records.
• If you attempt to change the value of the primary key in the primary table, Access pre-
vents this change if matching records exist in a related table. However, if you choose the
cascade updates option , Access permits the change in value to the primary key and
changes the appropriate foreign key values in the related table, thereby eliminating the
possibility of inconsistent data.
• When you attempt to delete a record in the primary table, Access prevents the deletion if
matching records exist in a related table. However, if you choose the cascade deletes
option , Access deletes the record in the primary table and also deletes all records in
related tables that have matching foreign key values. Note, however, that you should
rarely select the cascade deletes option, because setting this option might cause you to
inadvertently delete records you did not intend to delete.
Now you’ll define a one-to-many relationship between the Employer and Position tables
so that you can use fields from both tables to create a query that will retrieve the informa-
tion Matt needs. You will also define a one-to-many relationship between the NAICS (pri-
mary) table and the Employer (related) table.
Defining a Relationship Between Two Tables
When two tables have a common field, you can define a relationship between them
in the Relationships window. The Relationships window illustrates the relationships
among a database’s tables. In this window, you can view or change existing relation-
ships, define new relationships between tables, and rearrange the layout of the tables
in the window.
You need to open the Relationships window and define the relationship between the
Employer and Position tables. You’ll define a one-to-many relationship between the two
tables, with Employer as the primary table and Position as the related table, and with
EmployerID as the common field (the primary key in the Employer table and a foreign key
in the Position table). You’ll also define a one-to-many relationship between the NAICS
and Employer tables, with NAICS as the primary table and Employer as the related table,
and with NAICSCode as the common field (the primary key in the NAICS table and a for-
eign key in the Employer table).