Understand Cascade-to-Null Feature and How it Can Allow the Use of Null Foreign Key

Here we understand the Cascade-to-Null feature in MS Access and how to use it for allowing Null values in foreign key fields.

Indexed fields in MS Access are usually not Nulls mainly because the fields have been set with a ‘Required’ property, to ensure that there are values entered for all fields, irrespective of the data type. But as the case maybe there can be situations where having a Null foreign key can actually be of some help. An example can be of a situation where you need to flag a few records or change levels of status for a bunch of records, which in-turn will make changes to the batch as it will then not be required for reverting to Null value, which is already waiting to delete unwanted records.

Cascade-to-null Feature

If you attempt to delete records that are present on the ‘one’ sided table that has related values on the ‘many’ sided table, you will not be able to delete it. This delete operation fails because you have set the ‘Enforced Referential Integrity’ option. This makes sure that data values are maintained at all times. If you wish to take control of the order for all deleted records, you would need either the VBA code or several action queries. To help you avoid the use of either of these options, MS Access provides you with a Cascade-to-Null feature. This is a part of the MS JET engine which controls the Null values and should be enabled.

Note: You will be able to set this feature only if you have set the Enforced Referential Integrity in the Relationship Window.

Setting Null Value to Foreign key using Cascade-to-Null Feature

After selecting the Enforce Referential Integrity in the Relationship Window, you will find two more options:

• Cascade Update Related Fields
• Cascade Delete Related Records

If you do not select either of these options, a third option would be implemented by default. However, for applying Null values to Foreign Key fields, there is a fourth way, which is explained below.

• You can opt for maintaining referential integrity even when deleting a record from the primary table, by setting Null values to foreign key fields for all records related to ones in the primary table.
• This is actually the option that makes use of a VBA code because it is practically not a part of MS Access.
• Once you have the VBA code, you can implement the following steps:

1. Select the tables you want to link to create and another table which can have the null values.
2. To link the selected tables, you will need the VBA code to be executed.
3. After executing the code, enter code ‘Call Relationship ()’ in Immediate Window.
4. The returning value will be the attribute for Cascade-to-null relation.

Once the relationship has been created, you can opt for inspecting it. You might not get to see any obvious differences with the default settings still appearing to be that of the current relationship. But when you enter any Nulls, it will handle it without any trouble.

We often design Access databases with lot of effort. Over time, a whole set of critical data records gets stored in the database, especially for businesses which use Access. Now we would never want all these data to get compromised due to an Access crash. To recover from such a situation it is advisable to remain prepared and invest in a cutting tool to deal with mdb corruption like DataNumen Access Repair. This remarkable application can effectively bring back all records stored in the database including attachments and indexes.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including SQL Server repair and excel recovery software products. For more information visit www.datanumen.com