Referential Integrity

Cascades

Introduction

At this time, we know that there is useful functionality to creating relations between tables as they allow the user to select existing information instead of typing it. This flow of information brings up issues about what happens if data that exists in a parent table gets deleted while such information has been made available to another table.

When manipulating data that is in a relationship, it is very important to make sure that data keeps its accuracy from one table or source to the other. To accomplish that goal, some rules must be established to “watch” or monitor the flow of information between two tables. Data or referential integrity is used to check that two tables are related through one field on each table used as the primary key and the foreign key, data entered in the foreign key of a child table must exist in the parent table or it would be rejected, only two fields of the same data type are used to establish a relationship between two tables, the tables involved in the relationship belong to the same database.

Practical Learning: Editing Relationships

1. Open the Music Collection1 database you were working on in the previous lesson
2. To display the Relationships window, on the main menu, click Tools -> Relationships…
3. To control the relationships, on the Relationships window, click the joining line between MusicCategories and MusicAlbums. Notice that it becomes thicker than the others
4. On the main menu, click Relationships -> Edit Relationship…
5. Click the Enforce Referential Integrity check box.
   Now, the database would like to know how you would handle data updating and deletion
6. Click the other two check boxes:
    
7. Click OK
8. In the Relationships window, right-click the joining line between the Artists and MusicAlbums tables:
    
9. Click Edit Relationship... from the popup menu
10. Click all three check boxes and click OK
11. In the Relationships window, double-click the joining line between MusicAlbums and AlbumTracks
12. In the Edit Relationship dialog box, click all three check boxes and click OK
13. Using one of the above three techniques, configure the joining line between the MusicAlbums and the Formats tables for a one-to-many relationship that performs both cascade update and cascade deletes
     
14. Save the relationships window and close it

The Many-to-Many Relationship

Although one-to-many is the most common type of relationship applied on tables, in some databases, you may need to create a relationship in which many records from one table (A) can have many related records in another table (B) and vice versa. This type of relationship is known as many-to-many. For example, in our Video Collection database:

• It is possible to have one video that has many actors. In this case, if we had created a field to receive actors in the Videos table, we would enter many names of actors in that one field but this type of database would not be professional
• At the same time, one actor can have participated in many videos. In this case, if we had created a field to enter the titles of videos in the Actors table, the field would have too many entries, making the table unprofessional

To implement this type of relationship, you can create what is called a junction table. A junction table is a table whose main purpose is to bring together fields from other tables, creating a type of cross relationship for the necessary fields. A junction table can be very helpful for data analysis and sub-forms/sub-reports:

A junction table is usually made of three or four fields (usually not less than three and usually not more than four; a classic junction table has only three fields). The first field, almost less engaged, is used as the primary key, the same type of field almost every table has. The other fields hold data that would emanate from other tables.

You can create a junction table either in Datasheet View or in Design View:

• To create a junction table in Datasheet View, you use the Lookup Column menu that would call the Lookup Wizard. From the Lookup Wizard, select the table that holds the desired field, then select the field itself, and click Finish. After adding the first column, repeat the same steps for each needed field
• To create a junction table in Design View, set the field's data type to Lookup Wizard and proceed the same way you would for the Lookup Column

You can also switch to either view to create a junction table. In other words, you can create one field in one view and create the other field in the other view.

1. Open the Video Collection database you started in Lesson 12
2. Create a new table, on the main menu, click Insert -> Table
3. From the New Table dialog box, double-click Design View 
4. Type VideoActorID as the name of the first field
5. Set its Data Type to AutoNumber
6. Right-click the VideoActorID name and click Primary Key
7. Save the table as VideosAndActors
8. Click the empty field under VideoActorID, type ActorID then press Tab, type L and press F6
9. In the first page of the Lookup Wizard, make sure the first radio button is selected and click Next
10. On the second page of the wizard, make sure Actors is selected and click Next
11. In the Available Fields list, double-click Actor and click Next twice
12. Change the field's label to Actor/Actress and press Enter
13. When asked to save the table, click Yes
14. Switch the table to Datasheet View
15. In Datasheet View, right-click Actor/Actress and click Lookup Column…
16. In the first page of the Lookup Wizard, make sure the first radio button is selected and click Next
17. From the list of tables, click Videos and click Next
18. From the Available Fields, double-click Title to select it and click Next
19. Make sure the Hide Key Column check box is marked and click Next
20. Change the label to Video Title and click Finish
21. Switch the table to Design View
22. Set the Description of VideoID to The title of a video
23. Set the Description of ActorID to An actor or actress who participated in this video
     
24. Save the table and switch it to Datasheet View
25. Perform data entry, on the Video Title column, click the arrow of the combo box and select A Few Good Men
26. Then, on the Actor/Actress column, click the arrow of the combo box and select Jack Nicholson
27. In the same way, complete the table as follows:
     
    
28. Close (and if necessary save) the table
29. Open the Relationships window, on the main menu, click Tools -> Relationship... 
30. Observe the names of tables on the window
    Right-click an empty area of the Relationships window and click Show Table... 
31. On the Show Table dialog box, double-click Directors table. Also, if there is a table on the Show Table dialog box that is not displaying in the Relationships window, add it
32. Close the Show Table dialog box, click the Close button
33. Position the VideosAndActors table between the Actors and the Videos tables
34. Drag DirectorID from Directors and drop it on top of DirectorID in Videos
35. Check the Enforce Referential Integrity check followed by the Cascade Update Related Fields and Cascade Delete Related Fields check boxes
     
36. Click Create
37. As done previously, double-click each joining line and enforce its referential integrity to both update and delete records in the Edit Relationship dialog box
     
38. If you are using Microsoft Access 2000 and later, on the main menu, click File -> Print Relationships...
     
    
39. Click the close button. When asked to save the report, click Yes. Change the name of the report to Video Collection Layout and click OK
40. Close the Relationships window (in Microsoft Access 97, if you are asked to save the window, click Yes). 

The One-to-One Relationship

A one-to-one relationship is the type of junction between two tables A and B so that one record in a table can have only one corresponding entry in table B and vice versa. This type of relationship is rarely used because it is similar to one table of records (i.e. you may as well simply create one table).

Subdatasheets

A sub datasheet provides a technique of creating or displaying relationships in a table in a Datasheet View. It can help you or the user see information that is stored in another table. For example, when using a music collection database, it can show the music tracks that are part of an album by displaying an album in a table as long as a relationship has been created and configured.

To use a sub datasheet, open a table or query in Datasheet View and click the + button on the first column of the desired record. Here is an example:

1. Open the Music Collection1 database
2. On the Database Window, from the Tables section, double-click the MusicAlbums table
3. Click the + button on the left of Tribute
    
   
4. Notice that the tracks of the music album display
5. Perform data entry, click the + button of the One Bright Day record
6. Click the empty field under # then type 1 and press Enter
7. Complete the list of tracks as follows:
    

   #	Track Title	Length
   1	Black My Story (Not History)	04:16
   2	One Bright Day	04:12
   3	Who Will Be There	04:10
   4	When The Lights Gone Out	04:09
   5	All Love	04:39
   6	Look Who’s Dancing	05:00
   7	Justice	04:12
   8	Love Is The Only Law	04:07
   9	Pains Of Life	03:46
   10	Urban Music	02:54
   11	Problems	04:02
   12	All You Got	04:19
   13	When The Lights Gone Out (Jamaican Stylee)	04:52

8. After viewing the table, close it

MOUS Topics

Exercises 

• Open the Watts A Loan database and configure the cascading of relationships as follows:
   
  
   
  Save and close the Relationships window

• Open the Yugo National Bank database and open its Relationships window. Enforce the cascades on relationships as follows and save it:
   
  
   
  If you are using Microsoft Access >= 2000, print the Relationships dialog box and save its report as YNB Main Diagram