Relationships

Introduction

In the previous section we looked at keys and foreign keys. Here, once we have actually created out database tables, we shall look at creating the relationships between the different tables, based on the key and foreign key system.

Creating relationships between tables

Go to top

We shall start with creating the relationship between the Author and AuthorBook tables. Remember that in the Author table the field Author Id is the key field of the table. In the AuthorBook table the field AuthorID is the foreign key for the relationship between the Author and AuthorBook tables. In order to actually create the relationships in the database we click on Database Tools/Relationships. If this is your first time opening the Relationships panel the dialogue box below appears.

Fig 1

This dialogue allows you to select the tables you wish to appear in the Relationships. In our case we select all of them and then click on the Add button.

Once we do this the Relationships panel will look as in Fig 5 below.

Fig 2

Here the tables are arranged in the order that the relationships will work. As an example the AuthorBook table is between the Author and Book tables since it relates to both. Similarly the Publisher table is next to Book since the two of them also have a relationship.

Creating the relationships is quite simple. In order to create the Author to AuthorBook relationship we simply click on the Author Id field in the Author table and drag it to the Author Id field in the AuthorBook table. Once you do this the following dialogue box appears automatically.

Fig 3

As you can see we have the Author Id field from the Author table on the left hand side of this dialogue, i.e. the One side of the relationship while the Many side of the relationship, i.e. Author Id of the AuthorBook table is on the right side.

Ensure that you check the checkbox Enforce Referential Integrity. Checking this box effects the system in that it will not allow any value into the Author Id field of the AuthorBook table unless the same value already exists Author Id field of the Author table.

Checking Cascade Update Related Fields means that if we change the value of an Author Id field in the Author table, the related fields in the AuthorBook table will be changed to the same value.

Checking Cascade Update Deleted Records means that if we change the delete a record in the Author table, the related fields in the AuthorBook table will be deleted as well. (You should be very careful when using this option. Don’t us it in the current database)

Once you are happy with the setup click on Create.

The relationships between the other tables are created in the same manner. Once they are created the relationships panel looks as below.

Fig 4

Exercise

Go to top

Using Fig 4 as an example discuss how enforcing referential integrity contributes towards data integrity in the database.