As you can see, the rules of referential integrity are extremely strict. Although they do help ensure the maintenance of data integrity, they can make it difficult to manage your database. For example, as the exercise you just did illustrates, you cannot delete a record in a primary table that has related records in a foreign table. But what if you want to delete such a record? Under what circumstances can it be deleted, and what are the consequences of taking this action?
Two options mitigate the strict rules of referential integrity: Cascade Update Related Fields and Cascade Delete Related Records. These choices are available from the Edit Relationships dialog box, and I describe them shortly. But let me emphasize at this point that it is by no means certain that you will want or need to implement either option.
Cascade Update Related Fields is relatively noncontroversial. Furthermore, if you exclusively use AutoNumber (that is, artificial) primary keys, it is irrelevant.
Cascade Delete Related Records, however, can be applied whether you use artificial or natural primary keys. It can help to maintain data integrity, but it also has the potential to delete a swath of records from your database. Opinions vary on whether its advantages outweigh its disadvantages, and you might decide not to apply it.
I first explain Cascade Delete Related Records simply because I can show you how it works using the currently open Classic TV database.
The Power of Cascade Deleted Records
The relationships you create tightly bind together the various tables of a database. Thus two tables that do not have a direct relationship can affect each other in profound ways: An action taken in one table can set off a chain reaction that moves swiftly through your database via these interlocking links. As I've implied, deleting a record in one table when the Cascade Delete Related Records option is in force can have major repercussions throughout the many tables of your database.
Cascading Deletes in Two Tables
Before I show you the devastating potential of Cascade Delete, let me start with a simple example of cascading deletes in two tables.
Cascading Deletes in Multiple Tables
This next example demonstrates the enormous force of Cascade Delete Related Records in a database bound together by a nexus of one-to-many relationships. I much prefer examples that have you do the work over those that give you a complicated narrative. But a comprehensive example of the power of the Cascade Deleted Related Records option involves numerous steps, as well as much tiling and scrolling, so I describe the process with figures. I also use the Northwind sample database instead of the Classic TV database because it gives you a better sense of the impact this option can have.
Consider the following example from the order-entry model of the Northwind database. You have Suppliers and Products tables. The Suppliers and Products tables have matching data in the SupplierID field. SupplierID is the primary key in the primary table of Suppliers; SupplierID is also a foreign key in the Products table. The two tables have a one-to-many relationship: A single supplier can supply many products, but any one product can have only one supplier (see Figure 4.8). Importantly, referential integrity has been enforced.
Figure 4.8. Three related tables of the Northwind database in the Relationships window.
One of your important suppliers, Tokyo Traders, goes out of business. You open the Suppliers table (or, more likely, the Suppliers formagain, let's leave that discussion for Chapter 6) and try to delete the record.
But Access won't let you. This supplier sold you several products, so there are several records with this supplier in the Products field (see Figure 4.9). Because a one-to-many relationship exists between the two tables and referential integrity has been enforced, Access won't let you delete a record on the "one" side of the relationship when there are related records on the "many" side.
Figure 4.9. The supplier Tokyo Traders (ID#4) has three related records in the Products table.
What would happen if you were able to delete the Tokyo Traders record in the Suppliers table? If you chose the Cascade Delete Related Records option, the delete would cascade to the Products table, and those records would presumably be deleted as well.
So far, I haven't told you anything newyou saw this process at work just now in the Classic TV example. But in Northwind, what will happen if you remove records in the Products table? The Products table has a one-to-many relationship with the Order Details table (refer to Figure 4.8). If you delete a supplier record in the Suppliers table, the delete cascades to records in the Products table, and the deletes in the Products table will, in turn, delete the Order Details records for those products. Or will they?
Cascade Delete Not Consistently Implemented
Let's see how these phenomena play out under various circumstances. Let's assume that the relationships between Products and Suppliers, and Suppliers and Order Details are as shown in Table 4.3.
Now let's assume that you try to delete a record in the Suppliers table. At first, it would seem that there would be no problem in deleting it because Cascade Delete Related Records is in force between Suppliers and Products (refer to Table 4.3). But note that Cascade Delete Related Records is not in force for the Products-Order Details relationship. So if you try to delete the Tokyo Traders record in the Suppliers table, you won't be able to.
Why? Because records in the Order Details table are related to records in the Products table, which, in turn, are related to the Tokyo Traders record. The records in the Products table cannot be deleted because referential integrity is enforced between Products and Order Details, but Cascade Delete is not chosen.
Because the products records cannot be deleted, the Tokyo Traders record in the Suppliers table cannot be deleted, either. As you can see, it's entirely possible that you'll be unsuccessful at deleting a record in a table because of a relationship that seems far removed from it. But that's part of the beauty of a relational database: This interconnectedness enhances data integrity.
Cascade Delete Consistently Implemented
Suppose you change the relationship between Products and Order Details so that Cascade Delete Related Records is now in force between these two tables as well. In that case, if you delete Tokyo Traders in the Suppliers table, its products are also deleted in the Products table, and the order details in which those products were included are also deleted (see Figure 4.10).
Figure 4.10. This example assumes that Cascade Delete Related Records is implemented between both Suppliers/Products and Products/Order Details. The delete of the Tokyo Traders record (ID#4) in the Suppliers table cascades to the Products table, deleting three product records. Among them is Longlife Tofu, whose records are deleted from the Order Details table.
Cascade Update Related Fields
Cascade Update Related Fields is the other option available in the Edit Relationships window that mitigates the rules of referential integrity. When compared with the potent and controversial Cascade Delete Related Records, it will probably seem a little colorless and humdrum. As I indicated earlier, this option affects only primary keys that do not have an AutoNumber data type. If you find the argument for AutoNumber primary keys convincing, you might not have much reason to implement Cascade Update Related Fields.
Nevertheless, if you do use natural (that is, non-AutoNumber) primary keys, this selection could prove useful. Here's an example that shows the option at work.