Session Date: Monday Apr 03, 2017

Database Relationships

---

[top/reload prep panel]

Microsoft Help

Use a copy of the PK from the one side of a relationship as a FK in the many side of a relationship. You can do this either manually, or by using the lookup wizard to modify the properties of the FK in the table itself.

Primary and Foreign Keys

A relationship requires the primary key of one table be inserted as a foreign key in a second table.

The link between the two like attributes is the relationship.

• tables created manually do not have set relationships
• primary and foreign keys should share the same name and must share the same datatype and field size
• after you have established a relationship, then you will only need to enter data in one table for the related tables to be updated with the new data. This will become more clear in queries which create dynamic tables of data.

To view/create relationships in Access, select Database Tools > Relationships (or select the Relationships icon on the toolbar).

[top]

Types of relationships

one-to-one (unusual)

In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This setup is hardly ever worth the effort. In most cases it causes a system performance overload. However, in some cases, this setup can actually improve performance. This is not information that you need to know about for this course. If you are interested in understanding more about one-to-one relationship setup and how it affects data, check out one-to-one relationships at databaseprimer.com.

one-to-many (most common)

in a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A

note that your table properties provide you advice about how to ensure the relationships are based on the proper field properties.

many-to-many (less common than one-to-many, but essential in many cases)

in a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A

in Access, this type of relationship is only possible by defining a third table (called a junction table)

[top]

Creating a relationship in Access

You have tables and you have the potential for a relationship, but until you actually link the tables together, the relationship is not yet established

Establish the relationship in the relationship window.

Once you have decided which way the relationship goes, create a copy of the Primary Key from the one side of the relationship and place it as Foreign Key in the many side of the relationship

1. you may need to do some work on the Foreign Key properties to ensure the field is repeatable in the table that is the many side of the one-to-many relationship.
2. you accomplish this be selecting the Primary Key with the left mouse key and then dragging the PK field to the Foreign Key field in the related table and then dropping it there
   • a relationship will appear in the Relationship window between the two tables
   • a lot of stuff is going on in the background in Access, but for our purposes, the creation of that line between the two fields establishes the relationship between them

[top]

Editing Relationships

Referential integrity

referential integrity is a system of rules that ensure relationships between records in related tables are valid, and that you don't accidentally delete or change related data

for two tables to retain their referential integrity (that is, so that the relationship between them remains healthy and correct), you should always enforce it.

Cascades

you can override the restrictions against deleting or changing related records and still preserve referential integrity by setting the Cascade Update Related Fields and Cascade Delete Related Records check boxes

• when the Cascade Update Related Fields check box is set, changing a primary key value in the primary table automatically updates the matching value in all related records
• when the Cascade Delete Related Records check box is set, deleting a record in the primary table deletes any related records in the related table

When you complete the editing of the relationship, your Relationship view will show you the proper relationship between the two tables.

[top]

Look Up Fields

Lookup lists:

Display values looked up from a related table

Value lists:

Same as a lookup list but consists of a fixed set of values you type in when the lookup is created. A value list should only be used for values that will not change very often and don't need to be stored in a table

[top]

Creating a look up field

In Design View.... (note how this example includes descriptions of each field to help the user understand the intent of the field).

1. click in the desired field in the "Data Type" column
2. select "Look Up Wizard"
3. press Next (let Access decide what to lookup)

If you have the field already related as a Foreign Key, you may have to temporarily delete the relationship in the relationship window in order to run the Lookup Wizard. To check if things worked, change views to Datasheet view.

Look Up Tables can be ...

1. "limited" meaning only data from the look up table can be entered,
2. or "not limited", meaning a person can enter data that is not in the lookup table (of course this depends on the tables' relationships)

1. click the appropriate Field Name
2. under the Field Properties area, select the "Lookup" tab
3. for the option "Limit to List", you can select Yes or No.

For this Access Project, you want "Yes" selected

Go to Task 5.02