ACC2000: Nulls Allowed in Foreign Key with Referential Integrity

• Microsoft Access 2000

SYMPTOMS

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

RESOLUTION

To prevent users from leaving the foreign key blank, set its Required property to Yes.

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 allows 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.

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

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb.



2. On the Tools menu, click Relationships.



3. Double-click the line connecting the Customer ID in the Customers table to the Customer ID in the Orders table.
   
   Note the following:
   
   
   1. The Table/Query and Related Table/Query both display CustomerID.
   
   
   
   2. The Enforce Referential Integrity check box is checked.
   
   
   
   3. The join is one-to-many.
   
   
   
   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.
   If prompted to test the existing data with the new rules click Yes.



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.

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

   This error message is due to the feature called the Lookup Field. For more information about this feature, click Microsoft Access Help on the Help menu, type "create a field that looks up or lists values in tables" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
   



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, click Microsoft Access Help on the Help menu, type "what is referential integrity" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: null compact cascade prb

Keywords          : kbdta GnlApp 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: May 13, 1999