Managing Referential Integrity in Microsoft Access

Referential integrity in Microsoft Access ensures that the relationships between tables remain consistent. It involves making sure that the data between two related tables matches perfectly, preventing the existence of orphaned records in related tables. This article will guide you on how to use relationships to manage referential integrity in Microsoft Access.

1. Open the Relationships Window: Open Microsoft Access, then click on the Database Tools tab on the Ribbon at the top of the application window. In the Database Tools group, select Relationships. This will open up the Relationships window.
2. Add Tables: If you haven't added tables to the relationship window yet, you can do so by clicking on Design > Show Table. You can then select the tables that you want to add and click Add. Once all the required tables have been added, click Close. The tables will now appear in the Relationships window.
3. Establish the Relationship: Drag the field from one table and drop it onto the matching field in the other table. This operation will open the Edit Relationships dialog box. You will typically match primary key fields from one table to a foreign key in another table.
4. Set Referential Integrity: In the Edit Relationships dialog box, you'll see the Enforce Referential Integrity checkbox. Click this checkbox to turn on referential integrity for this relationship. This will ensure that any changes made to the linked tables adhere to the rules of referential integrity. If you wish to apply cascading updates and deletes, select the Cascade Update Related Records and Cascade Delete Related Records options. Cascading updates ensure that when a primary key value is changed, all associated foreign key values are also changed. Cascading deletes ensure that when a record in the primary table is deleted, all associated records in the related table are also deleted.
5. Confirm the Relationship: Click Create or OK to establish the relationship and enforce referential integrity. You should see a line between the two fields in the two tables, indicating that they are related. The line may have '1' and '∞' symbols, which denote the "one-to-many" relationship.
6. Save and Close: Finally, click Close on the Relationships window. You'll be prompted to save the layout of the relationship window. Click Yes to save your changes.

By setting up relationships with referential integrity in Microsoft Access, you can ensure the consistency and accuracy of your data, preventing the creation of orphan records and keeping the data related in a logical and useful way. It's crucial to plan your database schema carefully before setting up relationships, to avoid potential issues and complications later.

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

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 maintained. 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.

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.