Chapter 3: Designing Bulletproof Databases
125
Generally speaking, however, there are very few reasons why the value of a primary key may
change. The example I give in the “Connecting the data” section, earlier in this chapter, of a miss-
ing Social Security number is one case where you may need to replace a temporary Social Security
number with the permanent Social Security number after employee data has been added to the
database. However, when using an AutoNumber or another surrogate key value, there is seldom
any reason to have to change the primary key value once a record has been added to the database.Choosing the Cascade Delete Related Records option
The Cascade Delete Related Records option instructs Access to delete all related child records when
a parent record is deleted. Although there are instances in which this option can be quite useful, as
with so many other options, cascading deletes comes with a number of warnings.For example, if you’ve chosen Cascade Delete Related Records and you try to delete a particular
customer (who moved away from the area), Access first deletes all the related records from the
child tables — Sales and SalesLineItems — and then deletes the customer record. In other
words, Access deletes all the records in the sales line items for each sale for each customer — the
detail items of the sales, the associated sales records, and the customer record — with one step.Perhaps you can already see the primary issue associated with cascading deletes. If all of a custom-
er’s sales records are deleted when the customer record is deleted, you have no way of properly
reporting sales for the period. You could not, for instance, reliably report on the previous year’s
sales figures because all the sales records for “retired” customers have been deleted from the data-
base. Also, in this particular example, you would lose the opportunity to report on sales trends,
product category sales, and a wide variety of other uses of the application’s data.It would make much better sense to use an Active field (Yes/No data type) in the Customers
table to indicate which customers are still active. It would be quite easy to include the Active
field in queries where only current customers are needed (Active = Yes), and ignore the Active
field in queries where all sales (regardless of the customer’s active status) are required.Tip
To use this option, you must specify Cascade Delete Related Records for all the table’s relationships in the
database. If you don’t specify this option for all the tables in the chain of related tables, Access won’t cascade
deletions.
In general, it’s probably not a good idea to enable cascading deletes in a database. It’s far too easy
to accidentally delete important data. Consider a situation where a user accidentally deletes a cus-
tomer, wiping out the customer’s entire sales history, including payments, shipping, backorders,
promotions, and other activities. There are very few situations where users should be permitted to
delete many different types of data as a single action.Viewing all relationships
With the Relationships dialog box open, select View ➪ All Relationships to see all the relationships
in the database. If you want to simplify the view you see in the Relationships window, you can