Microsoft Access Tips for Serious Users

Provided by Allen Browne, July 2006.  Last updated: April 2010.

Cascade to Null Relations

Abstract: Highlights a little-known feature in Access, where related records can be automatically set to Null rather than deleted when the primary record is deleted.

Introduction

Have you ever set up a table with a foreign key that is null until some batch operation occurs? A not-for-profit organisation might send thank you letters at the end of each period to acknowledge donors. The Donation table therefore has a LetterID field that is Null until a batch routine is run to create a letter for each donor, and assign this LetterID to each of the records in the Donation table that are acknowledged in the letter.

So the user can undo the batch, you end up writing code to execute an Update query on the Donation table to change LetterID back to Null for all letters in the batch, deletes the Letters from their table, and deletes the BatchID from the Batch table.

Well, that’s the way you used to code a batch undo! There is now a way to get JET (the data engine in Access) to automatically set the LetterID back to Null when the letters are deleted, at the engine level, without a single line of code. Cascade-to-Null was introduced six years ago, but has remained below the radar for most developers.

This article explains how to create this kind of cascading relation, with a simple example to use with Northwind, and a sample database (13KB zipped) illustrating both DAO and ADOX approaches.

But first, a quick review of Nulls in foreign keys.

Referential Integrity and Nulls

When you create a relationship in Access, you almost always check the box for Referential Integrity (RI). This little check box blocks invalid entries in the related table, and opens the door for cascading updates and deletes.

What that check box does not do is prevent Nulls in the foreign key. In most cases, you must block this possibility by setting the Required property of the foreign key field in its table. But there are cases where a Null foreign key makes good sense. Batch operations like the receipt letters above are common. Even for something as simple as items in a category, you might want to allow items that have no category, so the CategoryID foreign key can be Null.

What is Cascade-to-Null?

We have mentioned three ways the database engine can enforce referential integrity:

• Normal: Blocks the deletion or alteration of entries in the primary table if they are used in the related table.
• Cascading Update: Automatically updates all matching entries in the related table when you change an entry in the primary table.
• Cascading Delete: Automatically deletes all matching entries in the related table when you delete an entry in the primary table.

There is a fourth way the database could maintain RI: when a record is deleted from the primary table, it could set the foreign key field of all related records to Null.

Benefits of Cascade-to-Null:

1. Related records are not lost!
2. Integrity is maintained. (There are no records with an invalid foreign key.)
3. The Null value in the foreign key perfectly represents the concept of unknown or unspecified.

Imagine a user created a goofy category in Northwind, and assigned it to several products. You need to delete the category, but without losing the products. With this kind of relation between Categories and Products, you can just delete the category, and all affected products become uncategorised. No code. No update queries. No testing: the engine takes care of it for you.

This is cascade-to-null: when the primary record is deleted, the foreign key of the matching records is set to Null automatically.

Creating a Cascade-to-Null relation

How has a feature this great remained unknown for most developers? Microsoft gave us the feature in Access 2000, but they never updated the interface. There is no Cascade-to-Null check box in the Edit Relationships window. You can only create this kind of relation programmatically.

As the example below demonstrates, the code is very simple. These steps work with Northwind to replace the relation between Products and Categories with a cascade-to-null.

1. Open Northwind.mdb in Access 2000 or later.
2. Open the Relationships window.
   In Access 2000 - 2003, choose Relationships from the Tools menu.
   In Access 2007 and later, click Relationships on the Database Tools tab of the ribbon.
3. Delete the existing relation between Products and Categories: right-click the line joining them, and choose Delete. Close the Relationships window.
4. Create a new module.
   In Access 2000 - 2003, select the Modules tab of the Database window, and click New.
   In Access 2007 and later, choose Module (right-most icon) on the Create ribbon.
5. Paste the code below into the new module.
6. Run the code: open the Immediate Window (Ctrl+G), and enter:
       Call MakeRel()
   The response will be the value for the Cascade-to-Null relation attribute:
       8192

Here's the code:

'Define the bit value for the relation Attributes.
Public Const dbRelationCascadeNull As Long = &H2000
Public Function MakeRel()
    'Purpose: Create a Cascade-to-Null relation using DAO.
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field

    Set db = CurrentDb()
    'Arguments for CreateRelation(): any unique name, primary table, related table, attributes.
    Set rel = db.CreateRelation("CategoriesProducts", "Categories", "Products", dbRelationCascadeNull)
    Set fld = rel.CreateField("CategoryID")  'The field from the primary table.
    fld.ForeignName = "CategoryID"           'Matching field from the related table.
    rel.Fields.Append fld                    'Add the field to the relation's Fields collection.
    db.Relations.Append rel                  'Add the relation to the database.


    'Report and clean up.
    Debug.Print rel.Attributes
    Set db = Nothing
End Function

To test it, open the Categories table and enter a new category, with a name such as "Goofy Food", and close. Open the Products table, and change the Category for a couple of products to this new category, and close. Then open the Categories table again, and delete the Goofy Food category. You will see this dialog:

Choose Yes. Open the Products table, and you see that the products that you previously placed in the Goofy Food category are now uncategorised. Deleting the Category caused them to cascade to Null.

(Note that Access does not have a dialog for Cascade-to-Null, so it uses the Cascade-Delete message.)

Maintaining Cascade-to-Null relations

Since the engine is maintaining the integrity of your data, this kind of relation means there are fewer update queries to execute. This in turn means less code to write, since the engine takes care of this for you.

But what if someone else needs to rebuild the database at some stage? Since the interface cannot show them that cascade-to-null relations are in force, they may recreate the tables and have no idea that your application relies on this type of cascade. You need a way to document this, and ideally it should be visible in the Relationships window.

Create a table purely for documentation. The table will never hold records. To ensure it shows in the Relationships window, create a relation to other tables, so it is not only saved in the Relationships view now, but shows up when the Show All Relationships button is clicked.

The field names can be anything, but since the goal is to catch attention, you might create a sentence using odd names reserved words:

Then open the Relationships window (Tools menu), and add the table. Drag the CategoryID field from the Categories table to the Id field in your new table, and create the relationship.

A Real World Example

Cascade-to-Null is useful beyond the simple "category" example above. In fact, it is worth considering in any relation where the foreign key is not required.

For example, you may have sales dockets that need to be collated into an invoice for each client at the end of the month. Since the sales dockets will become line items of an invoice, they have an InvoiceID foreign key that is null until the invoices are generated. The new invoices will be assigned a batch number, so the user can undo the entire batch if something goes wrong, fix the data, and run the batching process again.

Using a cascade-to-null relation between the invoice and the original docket record means that if you delete an invoice (or the whole batch), Access automatically updates all the sales items back to null. Next time the batch process is run, your code recognises that the sales records are not part of an account, and so they pick up those records automatically.

You probably have a cascading delete between your Batch table and Invoice table. So, you can now delete a single batch record: the related invoices are deleted, and the original sales dockets are cascaded to Null. No code. No chance of making a mistake: it is all maintained by JET.

Conclusion

Anything that operates at data engine level is simple to implement, safe, and maintenance-free for the developer. Once you start using this kind of delete rule, it is invaluable.