Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 208391 - Last Review: June 30, 2004 - Revision: 2.0
ACC2000: Nulls Allowed in Foreign Key with Referential Integrity
This article was previously published under Q208391
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 even if referential integrity is enforced.
To prevent users from leaving the foreign key blank, set its
Required property to Yes.
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
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.
- Open the sample database Northwind.mdb.
- On the Tools menu, click Relationships.
- Double-click the line connecting the Customer ID in the Customers table to the Customer ID in the Orders table.
Note the following:
- The Table/Query and Related Table/Query both display CustomerID.
- The Enforce Referential Integrity check box is checked.
- 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.
- 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.
- 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.
- Click OK.
- 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
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.
APPLIES TO
- Microsoft Access 2000 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please