Overriding Referential Integrity

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.

1.	Choose Tools, Relationships to open the Relationships window.
2.	Double-click the line between Genres and Programs to open the Edit Relationships dialog box.
3.	Select Cascade Delete Related Records by clicking the check box. Click OK.
4.	Close the Relationships window.
5.	If necessary, maximize the Database window. Open the Programs table and then open the Genres tables. Minimize the Database window.
6.	Choose Window, Tile Horizontally. It doesn't matter whether the Programs table is above the Genres table or vice versa.
7.	Select ID#1 (Rural Comedy) record in the Genres table. Press Delete. This time you get a message telling you that you are about to delete one record in the table, as well as records in related tables (see Figure 4.6). Note also that the GenreID 1 in the Genres table has been deleted. Figure 4.6. A warning message tells you that you are about to delete records in the current table and in related tables. Program IDs 1 and 4 are related records in the Programs table with values of 1 (for rural comedy) in the GenreID field.
8.	Click Yes to confirm the delete. The record is deleted in the Genres table, which is on the "one" side of the relationship. In the related Programs table, the two rural comedy records in Figure 4.6 are now also shown as having been deleted (see Figure 4.7). These records will not be there the next time you open the Programs table. Figure 4.7. Cascade Delete Related Records is selected, so when you delete a record on the "one" side of the relationship, records in the related table are deleted as well.
9.	Close both tables and close the database.

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.

Table 4.3. Relationships and Options Selected in Northwind Database

	Suppliers-Products	Products-Order Details
Relationship[*]	Yes	Yes
Enforce Referential Integrity	Yes	Yes
Cascade Delete Related Records	Yes	No

^[*] I've included Relationship in the table just for clarity. As you know, you can't enforce referential integrity unless a relationship has already been established.

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.

Q & A

Q1:

Wait a minute. Some of those order details records you just deleted might be for orders that haven't yet been paid foror even shipped. And I might still have some unsold Tokyo Traders products hanging around in my warehouse. Just because a supplier goes out of business doesn't mean I haven't already sold some of its products, or might not do so in the future. So why am I deleting these records in the Products and Order Details tables? Don't I still want those records for discontinued products to be in my database?

A1:

You're making the mistake of trying to be thoughtful and realistic when I'm trying to teach you database principles in a vacuum. Seriously, all kinds of database design issues must be considered in creating a fully functioning order entry system, and these go far beyond this brief introduction to database design. It might well turn out that your solutions demand that Cascade Delete Related Records not be implemented. But I want you to understand how the various options work.

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.

1.	Make a copy of the sample Northwind.mdb database. (It's in a folder named something like Program Files\Microsoft Office\OFFICE11\SAMPLES, or use Windows Search to find the file.) It is always a good idea to make a copy of the Northwind sample database to work with. In this case, it is especially important because you will be slightly modifying one of the tables.
2.	Open the database. In the Main Switchboard, click Display Database Window.
3.	Choose Tools, Relationships to view the Relationships window.
4.	Double-click the line between Customers and Orders.
5.	In the Edit Relationships dialog box, note that Cascade Update Related Fields is enforced. Click Cancel.
6.	Close the Relationships window.
7.	In the Database window, open the Customers table. Note that the CustomerID is the primary key; it is a Text field that consists of five letters.
8.	In the Database window, open the Orders table in Design view. Click in the CustomerID row. In the lower pane, click the Lookup tab and click in Display Control. Open the drop-down menu and choose Text Box. Save your changes. I had you modify the display for the CustomerID field so that the CustomerID itself is shown instead of the Customer name. (Don't worry if you have no idea why or what you just did. In the next chapter, you'll learn about lookup fields, and you'll understand that I merely asked you to change the display of the field so you can see the actual values that are stored.)
9.	Click View to switch to Datasheet view. Select the Customer field. Choose an Ascending (A-Z) sort so that the customer IDs are in alphabetical order.
10.	Minimize the Database window.
11.	Choose Window, Tile Horizontally. Your screen should look like Figure 4.11. (If your Customers table is below the Orders table, that's finethe table order doesn't make any difference.) Figure 4.11. The CustomerID identifies the customer in both tables. Note that the first record in the Customers table has the primary key ALFKI. Note that the first six orders in the Orders table were made to this customer.
12.	In the first record of the Customers table, edit the value to ALFZZ.
13.	Click anywhere in the next row. In the Orders table, the CustomerID has been updated to ALFZZ (see Figure 4.12). Figure 4.12. Because Cascade Update Related Fields is in force, you can edit a primary key with related records. The related records are updated with the change.
14.	Close the Customers and Orders tables. If you're asked to save design changes, click No.