SQLConfiguration SQLConfiguration

Database Relationships  «Prev  Next»
Lesson 5 Managing referential integrity
Objective Use relationships to manage referential integrity.

Managing Referential Integrity in Microsoft Access

Referential integrity is more complicated as a name than as a concept.
Referential integrity is the concept of making sure your relationships:
do not (a)get broken, or (b) end up with only one side of the data displayed. An example of this would be by creating a relation between Clients and Projects tables. By checking the Enforce Referential Integrity check box on the Edit Relationships dialog, users will not be able to:
Add a project record to the Projects table without specifying an existing client record in the Clients table (If a user tries to do so, he or she will get the following error message):

You cannot add or change a record because a related record is required in table 'Clients'

Delete a record in the Clients table if even one record exists in the Project table for the client:
The record cannot be deleted or changed because table 'Projects' includes related records
Referential integrity requires that relationships among tables be consistent. For example, foreign key constraints must be satisfied. You cannot accept a transaction until referential integrity is satisfied.
By using referential integrity, you are making sure that you minimize the “garbage in/garbage out” issue, making sure your sets of data entered into tables relate to each other correctly.
You will also get this message if you try to change a field value that has a relationship based on it. An example of this is that sometimes people will use codes for things such as loan types. If they try and change the code in the table where the code is a primary key (the one side of a one-to-many relationship), Access will not let them.
In the next lesson, you will learn how to set the Cascade Update and Cascade Delete options for relationships.

Using an event procedure to confirm record deletion

Although you can use the Delete button on the Records group of the Home tab of the Ribbon to delete a record in a form, a better practice is to provide a Delete button on the form. A Delete button is more user-friendly because it provides a visual cue to the user as to how to delete a record. Plus, a command button affords more control over the delete process because you can include code to verify the deletion before it is actually processed. Or you might need to perform a referential integrity check to ensure that deleting the record does not cause a connection to the record from some other table in the database to be lost. Use the MsgBox() function to confi rm a deletion. cmdDelete's event procedure uses MsgBox() to confirm the deletion. When the cmdDelete_Click() event procedure executes, Access displays a message box prompt.
Notice that the message box includes two command buttons: Yes and No. Access displays the prompt and waits for the user to make a selection. The record is deleted only when the user confi rms the deletion by clicking the Yes button.