| In an ancient Microsoft Access book whose author was apparently addicted to TV, there is a listing in the index for something called "residential integrity." So it might be worthwhile to emphasize that the term I'm about to discuss is referential integrity and that it has nothing to do with property maintenance or neighborhood beautification. Referential Integrity at Work The Access Help definition of referential integrity is "a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data." That's a start. But the best way to understand referential integrity is to see it at work, which I hope the following example accomplishes. 1. | In the Database window, click the Forms tab and open the Programs form.
Why am I asking you to look at a form based on the Programs table instead of the Programs table itself? Although tables are the foundation of your database, you usually use forms to enter, edit, and delete data. (These issues are further developed in Chapter 6, "Enter, Edit, and Display Data.") Because forms are more versatile, I've used a form to help you understand how referential integrity works.
| 2. | Maximize the Programs form. Right-click in any empty area of the form and choose Datasheet View from the shortcut menu (you can also choose View, Datasheet View).
The form is now in Datasheet view, so it appears as a grid. It has the same columns, the same rows, and the same values as the underlying Programs table. For now, you can consider the Programs form to be the equivalent of the Programs table.
| 3. | In the first record, click in the GenreID column. Click the drop-down arrow to see the available values (see Figure 4.3).
Figure 4.3. You would ordinarily use the combo box for GenreID to select and display the actual genre name such as Rural Comedy instead of the meaningless ID number. I have shown ID numbers in the GenreID field only for purposes of this example. 
I created this little box of values, known as a combo box, so you can see the names of the genres. I was able to create it because the Genres and Programs tables have matching data through the GenreID field. Note that the box contains all the values from the Genres table. The design of this combo box is artificial, and I'm using it only for teaching purposes. (Combo boxes are discussed in Chapter 11, "Forms/Subforms.")
| 4. | Edit the value in GenreID for the first record to 9.
As you saw from the combo box, there is no GenreID 9 in the Genres table.
| 5. | Click anywhere in the next record. Access has saved your change.
Even though in the previous section you established a relationship between the Programs and Genres tables, Access allowed you to change the GenreID to a value that is not in the Genres table.
| 6. | Edit the GenreID in the first row back to 1. Close the form.
| 7. | Choose Tools, Relationships to open the Relationships window.
| 8. | Double-click the line between Genres and Programs to open the Edit Relationships dialog box.
You can also right-click the line and choose Edit, Relationships.
| 9. | Select Enforce Referential Integrity.
For future reference, note that the Cascade Update Related Fields and Cascade Delete Related Records options, which had been previously grayed out, are now available because referential integrity is enforced.
| 10. | Click OK.
Look at the line that connects the field lists of Programs and Genres in the Relationships window. As in Figure 4.1, there is now a 1 near the primary key on the "one" side of the relationship. There is also an infinity sign on the "many" side.
| 11. | Close the Relationships window.
| 12. | From the Database window, open the Programs form. Right-click the form and choose Datasheet View from the shortcut menu.
| 13. | Edit the GenreID in the first record (for The Andy Griffith Show) to 9. Click in the next record.
Figure 4.4 shows you the error message you get. Because referential integrity is now enforced, Access won't let you edit the value because there is no record with the GenreID 9 in the Genres table.
Figure 4.4. Because referential integrity is enforced, Access allows you to enter only values in the foreign key of the Programs table that are in the primary key of the Genres table. 
| 14. | Click OK. Press Esc, or edit the GenreID back to 1 and close the form.
|
The Rules of Referential Integrity I think you can see that enforcing referential integrity has enormous consequences for data integrity. It can ensure that no customer is entered in an order that isn't on an approved customer list. It can make certain no product can be bought whose supplier isn't included on a list of approved suppliers. It can make sure no product can be offered for sale that isn't in a table of available products. Let me state the rules of referential integrity more formally. The Necessary Conditions The following conditions must be in place to enforce referential integrity: The matching key from the primary table (in the case you just saw, GenreID) must be a primary key or a unique index. Let's not worry about indexes right now, but rather focus on the fact that GenreID is indeed the primary key of the primary table. The related fields must have the same data type. There is one extremely important proviso to this rule: A field with an AutoNumber data type can be related to a field that has the Number data type and a FieldSize property setting of Long Integer. It is this qualification that you just saw at work. The GenreID field in the primary table of Genre has the AutoNumber data type. The GenreID field in the related table Programs has the Number data type and the field size of Long Integer. I introduced data types in Chapter 1, "Getting Started." A discussion of the FieldSize property awaits you in Chapter 5, "Building Tables." But I think you'll recall from high school math that an integer is a whole number and doesn't contain a fraction. Thus, values of 1, 2, 3, and so on in the primary key of the primary table match values of 1, 2, 3, and so on in the foreign key of the related table if the data type is Number and the field size is Long Integer. Thus, referential integrity can be enforced. Both tables are in the same Microsoft Access database.
The Consequent Outcomes When referential integrity is in force, the following applies: You can't enter a value in the foreign key of the related table that doesn't exist in the primary key of the primary table. You just saw an example of this: The GenreID of 9 wasn't in the primary table, so you couldn't enter it in the related table. There's one more bit to this rule: You can enter a Null value in the foreign key, unless the Required property for the field is Yes (see Chapter 5 for more on the Required property). You can't change a primary key value in the primary table if that record has related records in the foreign table. If you stick with artificial primary keys that have an AutoNumber data type and cannot be edited, this rule will not be an issue for you. You can't delete a record on the "one" side of the relationship when there are related records in the "many" side of the relationship.
Related Records Can't Be Deleted Let's look at an example of how related records on the "many" side of the relationship prevent you from deleting a record on the one side when referential integrity is enforced. 1. | In the Database window, open the Programs form. Right-click in the form and select Datasheet View.
| 2. | Select the GenreID column.
| 3. | Click the A-Z button for an ascending sort.
The GenreIDs are in ascending order. Note that at least two records have the value of 1.
| 4. | In the Database window, open the Genres table.
| 5. | Minimize the Database window. Choose Window, Tile Horizontally.
| 6. | Select the ID#1 (Rural Comedy) record in the Genres table. Press Delete.
The warning message (see Figure 4.5) tells you that you cannot delete the record because there are related records in the Programs table. The Genres tables is on the "one" side of the relationship, and several records in the Programs table on the "many" side are rural comedies.
Figure 4.5. Genre ID#1 cannot be deleted in the Genres table because there are related records in the Programs table. (It doesn't matter whether the Programs table is positioned above the Genres table or vice versa.) 
| 7. | Click OK.
| 8. | Close the Programs form; if asked, do not save the design changes. Close the Genres table.
|
|
