Provided by Allen Browne, July 2006. Last updated: April 2010.
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.
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.
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.
We have mentioned three ways the database engine can enforce referential integrity:
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:
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.
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.
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.)
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:
Field Name | Data Type | Description |
* * * WARNING * * * | Text | Informational only: no data. |
Cascade | Text | |
to | Text | |
Null | Text | |
Relations | Text | |
Exist | Text | |
On | Text | |
Products | Text | |
And | Text | |
Categories | Text | |
Id | Number | Primary key |
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.
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.
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.
Home | Index of tips | Top |