Microsoft Office Tutorials and References
In Depth Information
Establishing Relationships Between Database Tables
2. Select the Enforce Referential Integrity check box.
If you don’t select this box, the relationship between the tables is
indeterminate, instead of being a one-to-many relationship. Referential
integrity (another hideous database term!) has to do with whether values in
the two different fields corroborate each other.
3. Select Cascade options if you so choose.
One of these options is excellent, and the other is dangerous:
• Cascade Update Related Fields: If you change a value on the “one”
side of the relationship, a matching value on the “many” side changes
as well to preserve referential integrity. For example, if you create a
multifield primary key of First Name and Last Name and then change
the name of someone, the related fields in the other table change
automatically to preserve referential integrity. This is a great way to
make sure that information is up to date.
• Cascade Delete Related Records: If you delete a record in the “one”
table, all records in the “many” table to which the deleted record is
linked are also deleted. For example, if you delete an employee from
the “one” table, all records in the “many” table that include that
employee are deleted! Access warns you before making the deletion,
but still! This option is dangerous, and I don’t recommend selecting it.
4. Click the Create button to forge the relationship.
In the Relationships window (refer to Figure 2-11), a line is drawn
between the table fields. The number 1 appears on the “one” side of the
relationship and the infinity symbol (∞) appears on the “many” side.
After you create a one-to-many relationship between tables with the Enforce
Referential Integrity check box selected, you can’t enter a value in the
“many” table unless it’s already in the “one” table. For example, suppose
that the “one” table includes a primary key field called Employee Number,
and this field is linked to a field in the “many” table that is also called
Employee Number. If you enter an Employee Number in the “many” table
that isn’t in the “one” table, Access warns you that it can’t be done without