Microsoft KB Archive/98789: Difference between revisions
m (Text replacement - "<" to "<") |
m (Text replacement - ">" to ">") |
||
Line 68: | Line 68: | ||
<div class="errormessage"> | <div class="errormessage"> | ||
Can't add or change record. Referential integrity rules require a related record in table <one-side table | Can't add or change record. Referential integrity rules require a related record in table <one-side table>. | ||
</div> | </div> | ||
Line 80: | Line 80: | ||
<div class="errormessage"> | <div class="errormessage"> | ||
You can't add or change a record because a related record is required in table <one-side table | You can't add or change a record because a related record is required in table <one-side table>. | ||
</div> | </div> |
Latest revision as of 10:27, 21 July 2020
Article ID: 98789
Article Last Modified on 1/18/2007
APPLIES TO
- Microsoft Access 1.0 Standard Edition
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
This article was previously published under Q98789
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 -----------------------
In Microsoft Access 97 ----------------------
This behavior differs from that of earlier versions of Microsoft Access. Microsoft Access version 2.0 allows you to save a record in which at least one field of the foreign key contains a Null value and at least one other field of the foreign key contains data.
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:
170779 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.
- Open the sample database Northwind.mdb (or NWIND.MDB).
- On the Tools menu (or Edit menu in version 1.x or 2.0), click Relationships.
- 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:
- The Table/Query and Related Table/Query both display CustomerID.
- The Enforce Referential Integrity check box is checked.
- The join is One-To-Many
- The Add button is unavailable. (Version 1.1 only)
- 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. - 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:
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:
- 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:
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.
Additional query words: null compact cascade
Keywords: kbinfo KB98789