Unit 6. Relationships (I)

In this unit we will see how to relate tables and the different types of relationships that can exist between two tables in a database.

If you do not know what a relationship is in a database, here we will introduce you to some basic concepts to understand relationships better.

Creating the first relationship.

To create relationships in Access2003 firstly we need to position ourself in the Relationships window, we can opt for:

in Database window, drop down Tools menu, and select Relationships... option.

or,

Click on the button on the toolbar.

The Show table dialogue box will appear:

Click on one of the tables required in the relationship and click on the Add button; or double-click the name of the table.

Repeat the previous step to add the second table, and so on.

Finally click on the Close button to finish adding tables.

Now the Relationships window will appear with the tables added before.

To create the relationship:

Drag the field of the principal table to the equivalent field in the related table. In our case drag Number (in Customers table) to Customer (in Invoices table).

Normally you drag the primary key of the primary table.

To relate tables with two or more fields, first select the fields mantaining CTRL key down, and then drag them.

The Edit relationships dialogue box appears next:

At the top should be the names of the related tables (Customers and Invoices) and below this the names of the related fields (Number and Customer). Observe! they always have to be the same kinds of fields containing the same types of information.

Observe at the bottom the Relationship type assigned depends on the charateristics of the related fields (in our case One-to-Many)

Activate the Enforce Referential integrity by clicking on it.

If desired, the boxes Cascade Update Related Fields and Cascade Delete Related Records can be activated.

If you want to know more about referential integrity and Cascade procedures clic here .

To terminate, click on the Create button.

The relationship is created and will appear in the Relationships window.

To practice these operations you can perform the Step by step Exercise Step by step exercise on creating relationships.

Page. 6.1