ACC2000: Nulls Allowed in Foreign Key with Referential Integrity (208391)

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.

Steps to Reproduce Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

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