Table
Relationships
The information
in the tables of your database can be related to one another,
as demonstrated in the Introduction seminar when tables were
linked in a query. However, certain types of relationships
may exist between one table and another. In these cases, they
can be defined by using the Relationships button. 
The purpose for creating these relationships is to make data
entry more secure.
When you
wish to define table relationships, you add the tables to
the Relationships window. Here are the four tables in our
sample database (including the linked Employees table) in
the relationships window.
There
are two main types of relationships that can be defined:
- ONE-TO-ONE:
In this relationship, a record in the primary (parent) table
has a single corresponding record in the related (child)
table. For example, you may have a table containing employee
addresses and phone numbers, while another table has employee
salary information. There should be one record in the primary
table related to EXACTLY one record in the related table.
- ONE-TO-MANY:
In this relationship, a single record in the primary table
can be linked to several tables in the related table. For
example, a single member out of the Customers table may
have made many purchases in the Transactions table.
To make
these relationships, drag the field from the primary table
to the corresponding field in the related table. As an example,
we will drag the Customer ID field from the Customers table
onto the Customer # field on the Transactions table. <Click
here> for a video demonstration. (247K) When the connection
is made, the following dialog box appears.
Notice
the table and fields in the correct locations. The checkbox
below the list allows you to enforce referential integrity.
This means you CANNOT enter a record into the related table
(in this case, Transactions) without a correct Customer ID
from the primary table. (Customers) You can also choose to
Cascade Update (or Delete) the related fields. This will update
(or delete) records in the related table if any changes are
made in the primary table. Here is the Relationships window
with all tables joined together.
Note the
link between the Employees table and the Transactions table.
The one-to-many relationship cannot be defined, nor can referential
integrity be enforced. As this is a linked table, you CANNOT
enforce referential integrity across databases. <Click
here> to see a video demonstration of what happens
when you attempt to enter a Customer number that is not in
the Customers table. (459K)
|
