DBWebApplications 





Advanced Features  «Prev  Next»
Lesson 5 Managing referential integrity
Objective Use relationships to manage referential integrity.
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'
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
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.