ACC: Nulls Allowed in Foreign Key with Referential Integrity

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

Microsoft Access allows a Null value in a field that is the foreign key in a relationship if referential integrity is enforced.

To prevent users from leaving the foreign key blank in Microsoft Access 97, 7.0, or 2.0, set its Required property to Yes. To prevent users from leaving the foreign key blank in Microsoft Access version 1.x, add a table level validation rule to the table on the many side of the relationship. For an example, see the "Steps to Reproduce Behavior" section.

MORE INFORMATION

This behavior occurs whether you use a single-field primary key and foreign key or a multiple-field primary key and foreign key. However, Microsoft Access versions 7.0 and later allow a Null value in a field that is part of a multiple-field foreign key only if every other field of the foreign key also contains a Null value. If at least one field of the foreign key contains a Null value and at least one other field of the foreign key contains data, you receive the following error when you try to save the record.

   In Microsoft Access 7.0
   -----------------------

      Can't add or change record. Referential integrity rules require a
      related record in table <one-side table>.

   In Microsoft Access 97
   ----------------------

      You can't add or change a record because a related record is
      required in table <one-side table>.

For more information about Null values and multiple-field foreign keys in Microsoft Access versions 7.0 and later, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q170779
   TITLE     : ACC: Inconsistent Behavior with Multiple-Field Primary
               Key

Steps to Reproduce Behavior

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb (or NWIND.MDB in version 2.0 or earlier). You may want to back up the Northwind.mdb (or NWIND.MDB) file and perform these steps on a copy of the database.

1. Open the sample database Northwind.mdb (or NWIND.MDB).

2. On the Tools menu (or Edit menu in version 1.x or 2.0), click Relationships.

3. In Microsoft Access 97, 7.0 or 2.0, double-click the line connecting the Customer ID in the Customers table to the Customer ID in the Orders table. In version 1.x, In the Primary Table drop-down box, select the Customers table and in the Related Table drop-down box, select the Orders table.

   Note the following:

       a. The Table/Query and Related Table/Query both display CustomerID.
   

       b. The Enforce Referential Integrity check box is checked.
   

       c. The join is One-To-Many
   

       d. The Add button is unavailable. (Version 1.1 only)
   

   These characteristics indicate an existing relationship between the Customers and Orders tables; therefore, you will not be able to make an entry in the Customer ID field of the Orders table if that Customer ID does not exist in the Customers table.

4. Close the Relationships dialog box and open the Orders table in Design view. Select the CustomerID field and note the Required property. Change this property to Yes and save the table.

   NOTE: In Microsoft Access, versions 2.0, the Required property will be set to Yes by default. In Microsoft Access, versions 1.x, you will need to set a validation rule. See the Microsoft Access "User's Guide", version 1.1, page 43-46 for more information.

5. Open the Orders table in Datasheet view and try to change the Customer ID field of the first order to "XXXX" and press TAB to try to move to the next record. Note that you receive the following error message.

   In Microsoft Access 97 or 7.0:

         The text you entered isn't an item in the list.
   

   This error message is due to the new feature in Microsoft Access 7.0 and 97 called the Lookup Field. For more information about this new feature, search on "Lookup fields," and then "Create a field that looks up or lists values in tables" using the Microsoft Access Help Index.

   In Microsoft Access versions 1.x and 2.0:

         Can't add or change record. Referential Integrity rules require
         a related record in the table "Customers."
   

6. Click OK.

7. Delete the entry in the Customer ID field. Make sure the field is completely empty. Press TAB to move to the next record. You will receive the following error message:

        Field 'Orders.CustomerID' can't contain a null value
   

REFERENCES

For more information about data integrity, search the Help Index for "referential integrity," and then "What is referential integrity" or ask the Microsoft Access 97 Office Assistant.