Lessons Logo

Referential Integrity





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 (sometimes more than 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 otherwise 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 table.


Cascades on Related Records

After creating a legitimate relationship between two tables, you must then make sure that when data changes in the parent table, this change is reflected in the child table. For example, if a bank customer changes her last name after getting married or after a divorce, you should be able to change her name in one object (table) and the related objects, such as the one used to process her transactions would receive the changes without your having to make the change on each object (table). In the same way, when data is deleted, the objects that are related to it must also have that data deleted.

To enforce the rules of data integrity, Microsoft Access provides three check boxes in the Edit Relationship dialog box. First, if you want Microsoft Access to monitor data flow, you can click the Enforce Referential Integrity check box. This would make available two check boxes.

The Direction of a Relationship


The One-To-Many Relationship

As mentioned already, a relationship between two tables allow one table, the parent, to make its information available to another table (the child). Because in this case the user is asked to select information, it is likely that the same record on a parent table can be tied to various records in the child table. For example, one customer at a bank can deposit an amount of money today. The same customer can make another deposit tomorrow and even another deposit next month. In such a case, the relationship between the tables would show various entries of the same customer’s account number in the object (table) used to deposit money but with different transactions. This type of relationship is known as one-to-many because one entry in the parent table can result in many entries in the child table.

To create a one-to-many relationship, check all three referential integrity check boxes and click Create. The parent table would have a 1 sign on its side of the joining line. The child table would have the infinity symbol on its side of the joining line.

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. Check 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 the 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 it 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 originate 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.

Practical Learning: Creating Junction Tables

  1. Open the Video Collection database you started in lesson 12
  2. To 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. To 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. To 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 property sheet that is not displaying in the Relationships window, add it
  32. To close the Show Table property sheet, 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 reference 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 table A can have only one corresponding entry in table B and vice versa. Because this is similar to one table of records, this type of relationship is hardly used since you can as well simply create one table.


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 should 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:

The subdatasheet is not available in Microsoft Access 97


Practical Learning: Using a Sub Datasheet

  1. Open the Music Collection 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. To 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

Lesson Summary


MOUS Topics

S30 Display related records in a subdatasheet
S34 Enforce referential integrity
S45 Print database relationships



Watts A Loan

  • Open the Watts A Loan database and configure the cascading of relationships as follows:

    Save and close the Relationships window

Yugo National Bank

  • 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



Previous Copyright © 2002-2007 Yevol Next