CCNC/CCNC Module 5/Designing and creating tables/Table Relationships

---

Creating table relationships

Outcomes

Upon completion of this tutorial the learner will be able to: Create relationships between tables Design table relationships, including; one-to-many and many-to-many Apply rules to relationships to increase data integrity

All the work done in this designing and creating tables section has been focused on table design and creation. Excepting in unique situations every table in a database will be related to another table. Setting the rules around the table relationships is done by using the OO.o relationship designer.

Designing the relationship

This first relationship we will define the rules will be the one-to-many relationship. In our example we will be describing the relationship between the People to PhoneNumbers tables. The idea behind this relationship is that a person can have more than one phone number (i.e. home, work, mobile, pager, etc.)

Adding the tables

Figure 2. Relationships menu

The first step in setting the rules of a relationship is to open the Relationships designer. This is done through the Tools -> Relationships menu.

Figure 3. Select the tables

Figure 4. Relationship designer

The relationship designer will start with either; a dialog prompting to add tables or a display of the existing relationships. Which gets displayed depends if there are already existing relationships. If you are prompted to add tables add both the People and PhoneNumbers tables. After you close the add tables dialog the relationship designer screen will appear. It is within this window that you will design the relationships.

The toolbar of the relationship designer has two important buttons;

• one to add tables
• and another to add new relationships

Defining the relationship

Figure 5. defining the relationship

To define a new relationship just drag and drop the foriegn key field from one table over top of the primary key of the other table in the one-to-many relationship. In the example provided here the person_uid from the PhoneNumbers table is dragged onto the person_uid of the People table.

Edit the relationship

Figure 6. editing the relationship

To edit the properties of the relationship right click the line between the tables and choose the Edit... option.

Setting the rules

Figure 7. setting the rules

You want to make sure that the records between tables remains accurate once a relationship has been created. The way to do this is to enforce referential integrity. Editing the relationship properties is how referential integity is enforced. By default no action is taken on relationships.

By clicking on Update cascade you are ensuring that if a change is made in the Primary Key field it will make the changes in the other tables that that field is linked to.

Clicking on Delete cascade ensures that Base will delete all the records that are linked to that field. If this option is not selected you will not be allowed to delete a record that is linked to other records.

Test your knowledge

Table relationships

1. The query designer is used to create relationships? TRUE → Incorrect, the relationship designer is used. Click here to review the topic. FALSE → Absolutely Correct! 2. Which of these choices best describes the update options for a relation? (a) No action → Almost correct, '(e) All of the above' is the best answer. Click here to review the topic. (b) Update Cascade → Almost correct, '(e) All of the above' is the best answer. Click here to review the topic. (c) Set null → Almost correct, '(e) All of the above' is the best answer. Click here to review the topic. (d) Set default → Almost correct, '(e) All of the above' is the best answer. Click here to review the topic. (e) All of the above → Absolutely correct! (f) None of the above → Incorrect, '(e) All of the above' is the correct answer. Click here to review the topic. Your score is 0 / 0