Can A Foreign Key Be Null: Cascade to Null Relationships In Microsoft Access

Can A Foreign Key Be Null: Cascade to Null Relationships In Microsoft Access

The first thing to note here when asking the question can a foreign key be Null? is to clarify what a foreign key is, what is meant by the value Null and something known as setting the ‘Enforce Referential Integrity‘ option for your joined tables.

can a foreign key be null

Simply put:

A Foreign Key is way to index a field which can accept duplicate values that are found from the ‘many‘ side of a ‘one-to-many‘ relationship and cannot be left blank.

A Null value is an unknown value or a data entry which has no value to it which can happen with your text and date/time data types. When populating records (or importing data) into two or more tables with joins, sometimes missing data values (or Nulls) can impact on what record results your queries will return.

Enforce Referential Integrity is an optional setting that is only set via the Relationship Window and will be tested during data entry. It validates for any ‘many‘ sided records to enforce it has a parent record (from the ‘one‘ side) maintaining data consistency and account for all known records preventing missing links between two tables. With this option relaxed (being the default), no enforcement is made and can return missing records to your queries.

Can A Foreign Key Be Null: Cascade to Null Relationships In Microsoft Access

The normal practice for indexed fields (primary or foreign) should not be a null value and can be controlled by setting the ‘Required‘ property to a field forcing data entry values to be present.

However, there are cases where a Null foreign key makes good sense. For instance, you may want to flag records or change the status levels for a batch of records which may change as the batch is no longer required reverting back to a Null value ready for deleting any unwanted records.  Trying to delete records from the ‘one‘ sided table with related values (to the ‘many‘ sided table) will not allow you to delete these records if you had the ‘Enforced Referential Integrity‘ option set as data values must be maintained.

You would need to use either some VBA code or various action queries to handle the order of deleted records from two or more tables (again in the correct order).

Luckily, Microsoft Access has a neat feature which its engine (JET) can control Nulls and this is generally referred as ‘Cascade to Null’ but must first be enabled which is so only if you have enforced the referential integrity.

In the Relationship Window view when joining tables together, as you choose the option ‘Enforce Referential Integrity‘, it releases two other options as shown in the image below:

can a foreign key be null edit relationships

The two options:

Cascading Update: Automatically updates all matching entries in the related (‘many‘ sided) table when you change an entry in the primary (‘one‘ sided) table.

Cascading Delete: Automatically deletes all matching entries in the related (‘many‘ sided) table when you delete an entry in the primary (‘one‘ sided) table.

Of course, if you do not enable any of the above two options the third (or default) option will not stop any violations. The benefits of using the above means you maintain the data integrity, related records cannot be lost and the Null value is avoided thus being  represented correctly due to the database engine taking control.

However, there is a fourth way the database system could maintain referential integrity; when a record is deleted from the primary table (the ‘one‘ side), it could set the foreign key field of all related records to Null (and records are not lost).

This approach will require some VBA code as this option is not available in MS Access!

In my example, I’m going to link a product category table (called ‘Product_Categories‘) to the ‘Products‘ table so that each product can optionally be assigned a category with the common matching ID field (called ‘ProdCatID‘). See below:

can a foreign key be null product category

The linking of the two tables will need to be executed using the following VBA code which is added into a new module and run via the Immediate Window. Make sure you have first broken the link via the Relationship Window (if set) as this piece of code will set it for you.

can a foreign key be null product category vba code

(Make sure you have added the DAO library to this database).

Run the procedure with the following code in the Immediate Window:

Call CreateRelationship()

The value returned should be 8192 which is the Cascade-to-Null relation attribute value (just accept it!).

The above code has now created a relationship which you can of course go and inspect. You will see no obvious differences and the default appears to be the current relationship setting. However, it will handle Nulls going forward.

If you assign a few products with the same category and then go to the product category table and delete the one category, the usual prompt will appear:

can a foreign key be null delete prompt

Confirm this action and then revisit the products table and the deleted category will have disappeared returning the field’s value back to Null.

So can a foreign key be Null? Yes, it can but in a controlled environment maintaining database consistencies that otherwise would render fragmented and missing records and values.

This is a great improvement on how to manage unwanted values in related data sets and removes any coding or programming needs that some developers feel they would otherwise have to apply. As you now decide to remove an unwanted group or category values from a primary table, you now will see any related records that had been assigned to that group or category return its value to Null again.

Where have you used this feature in your Access database? Why not share your experiences with us here in the comments box below.

This entry was posted in Database Theory, Microsoft Training, MS Access, Tables and tagged , , , , . Bookmark the permalink.

4 Responses to Can A Foreign Key Be Null: Cascade to Null Relationships In Microsoft Access

  1. Ben says:

    Yes Elliott, this is a good strategy to provide a user-friendly application.
    If so, you may need to relax this type of relationship link and revert to using action queries to change and flag values (which is how I normally mark records).
    Also, you can still use this ‘Cascade to Null’ technique and have another table to store a log (audit trail if you like) of data value changes!Ben

  2. Giorgio,

    If I correctly understand Ben you can NOT enable ‘Cascade to Null’ using the Edit Relationship window; you can only do it using VBA code.

    Ben: This is the first time I have heard about this capability and am very excited to try it out. I envision many opportunities to use this technique to OPTIONALLY categorize an entity. Deleting the category would not delete the instance of the entity in the database.

    However, I would definitely prefer to trap the error that occurs, when an attempt is made to delete the entry in the category table, and provide a message that indicates that the categories of the entities will be set to null, rather than that they will be deleted.


  3. Ben says:

    Hello Giorgio,
    Oops!!! You are correct. This tool maintrains integrity and therefore will delete or update the many side so you can’t have nulls.
    However, what i didn’t mention (my apologies) is there is a hidden feature to enable this ‘Cascade-To-Null’ but it actually will require VBA code to achieve this.
    So what I’m going to do is re-write (and correct) this blog and dig into my archive for the code (it’s been a while since I last used it) and re-post this blog again.
    Please keep an eye out as I will make the changes by the end of week.
    Once again, thank you for pointing this out (going to sack the author now!) :)

  4. Giorgio Rovelli says:

    “this is generally referred as ‘Cascade to Null’ but must first be enabled”
    How do you enable it on the Edit Relationship window? To me the two options I see delete related records in the foreing key table if you delete records in the primary key table, not return their value to Null.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.