When you create relationships in Access, you have the option of enforcing referential integrity, which is a system of rules that helps ensure the validity of your data. Once you have chosen referential integrity, you can choose to modify these stringent and unforgiving rules by selecting two options: Cascade Update Related Records and Cascade Delete Related Records. In this tutorial, we�ll focus on Cascade Update, which we think is a little more difficult to understand. ../images/pixblue.gif Selecting Cascade Update lets you update (as in edit) a primary key, even though the key is on the one side of a one-to-many relationship and referential integrity is enforced. The updates you make to the primary key will be reflected in related records, which have matching data in the foreign key. (This technical explanation will become clearer as you work through the tutorial.)
Please note that selecting Cascade Update is irrelevant when the primary key is an AutoNumber field because an AutoNumber field cannot be updated. But even if you always use AutoNumber fields for your primary keys, we suggest that you work through this tutorial because it will help you gain a better understanding of the essential principle of referential integrity and its exceptions.
Following this tutorial will require you to edit relationships and data in the Northwind database, so we strongly recommend that you make a copy of the database file before proceeding. Navigate to the Northwind.mdb database in the Samples subfolder of your Microsoft Office folder. Copy Northwind.mdb to the Clipboard and paste it in another folder. Rename the file CopyOfNorthwind.mdb, which you can now open and use without worrying about changing the original file.
In the Database window with Tables selected in the left pane, double-click Customers to open that window. Note that the values in the Customer ID field have five characters, based on the customer name. Click the View button to go to Design view. In the first row, note that CustomerID is the primary key with a Text data type. Close the Customers Back in the Database window, select Tables in the left pane and double-click Orders. Note that the second column displays customer names. Click the View button to switch to Design view. Then, click anywhere in the second row, which has the CustomerID field. The Description states that this CustomerID is the "same entry as in the Customers table."
So, even though the Datasheet view shows customer names, the actual values in this field are the IDs. In other words, this field stores IDs but displays names. We want to focus on the IDs, so let�s edit Field Properties to display the IDs instead.
In the Field Properties pane, click the Lookup tab and click inside the Display Control field. Open the drop-down menu and choose Text Box. Click Save and then click the View button to switch to Datasheet view. The five-letter CustomerIDs now display in the Customer column. Close the Orders table.
In the Database window, choose Relationships from the Tools menu. Note that there is a one-to-many relationship between the Customers and Orders tables through the CustomerID field. The CustomerID in the Customers table is the primary key on the �one side� of the relationship, and the CustomersID field in the Orders table is the foreign key on the �many side� of the relationship.
Double-click the line between Customers and Orders. When the Edit Relationships dialog box opens, select Customers from the Table Query drop-down menu (see Figure 1). Below, you�ll see check marks in the Enforce Referential Integrity and Cascade Update Related Fields checkboxes. Deselect the Cascade Update Related Fields checkbox and click OK. Close the Relationships window.
Next, open the Customers table. In the first row, edit the five-letter Customer ID for Alfreds Futterkiste to change it to ALFTT. Click anywhere in the second row of the table, and you should see an error message appear on-screen indicating that Access won�t let you edit the primary key because referential integrity is enforced and there are related records in other tables. Click OK. Now try clicking the Close button (the X in the upper-right corner of the window). You should see the same error message appear on-screen; click OK. You then will see another message stating that you can�t save the record at this time. Click Yes to close the table. As you noticed, referential integrity was enforced so you could not edit a primary key.
From the Database window, choose Relationships from the Tools menu. Double-click the line between Customers and Orders. Select the Cascade Update Related Fields checkbox and click OK. Close the Relationships window.
Open the Customers table. In the first row, again try to edit the five-letter Customer ID for Alfreds Futterkiste to change it to ALFTT. Click in the next row and note that no error message appeared this time. Because you selected the Cascade Update Related Fields checkbox, you can edit the primary key. Save your change and close the Customers table.
In the Database window (with Tables selected in the left pane), double-click Orders to open the Orders table. Select the entire Customers column and click the A-Z button on the toolbar for an Ascending sort. As you can see, the ID for Alfreds Futterkiste has been updated to ALFTT to reflect the change you made in the CustomerID in the Customers table (see Figure 2). Close the table, save your changes, and exit Access.