Table Relationships

[Cyhill]

What is the benefit of setting up table relationships in the relationships
window? OR...what is at risk if this is not done?

Is it not essentially the same thing to assign the necessary relationships
within queries, etc?

 

[Jeff Boyce]

Although setting up the joins in queries will be automatic if you've set the
relationships between the tables, that is not the primary reason for doing
so.

When you establish relationships between tables in the relationship window,
Access prompts you for also setting Referential Integrity. By setting this,
you are ensuring that a "child" record cannot be added unless there is a
"parent" already in place. You can also use the settings to manage
cascading updates and cascading deletes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pat Hartman \(MVP\)]

Let me add to that - every well designed database makes use of declarative
referential integrity whenever possible. Some but not all the things that
the database engine will do for you:
1. Ensure a valid parent record if you have created a relationship and
checked the enforce RI box.
2. Cascade delete child records (delete the order details when you delete an
order) if you choose enforce RI and cascade delete. This can be a dangerous
feature in the hands of the unwary. Make sure you know what will happen
when you set this. You don't want to delete all customer addresses in
California if someone accidentally deletes California from the state table.
With RI in place but not cascade delete, RI will prevent you from deleting
California from the state list if there are any addresses with California as
the State.
3. Cascade update foreign key values when a primary key value changes (only
applies to non-autonumber primary keys) if you choose enforce RI and cascade
update.
4. Ensure that a date is a valid date or null if you define the field as a
date.
5. Ensure that the field is numeric and in a particular range (whatever the
specific field type holds) if you define the field as a numeric data type.
6. Enforce any validation rules. These may be range checks or value lists.

So, rather than attempt to enforce these type of rules in code behind a
form, define (declare) them to the database engine and it will enforce them
for you regardless of how the data is updated.