ACC: Inconsistent Behavior with Multiple-Field Primary Key
ID: Q170779
|
The information in this article applies to:
-
Microsoft Access versions 7.0, 97
SYMPTOMS
Novice: Requires knowledge of the user interface on single-user computers.
In Microsoft Access version 7.0 or later, you may see inconsistent behavior
when you define a relationship that enforces referential integrity, and the
"one-side" table contains a multiple-field primary key. You can create a
relationship, even though a record in the "many-side" table contains a Null
value in one of the foreign key fields. However, after the relationship has
been defined, you receive the following error message when you change
another record in the "many-side" table so that it also contains a Null
value in one of the foreign key fields:
You can't add or change a record because a related record is required
in table <table>.
In Microsoft Access 7.0, you receive the message:
Can't add or change record. Referential integrity rules require a
related record in <table.>
STATUS
Microsoft has confirmed this to be a problem in the Microsoft Access
versions listed at the beginning of this article. This problem no longer
occurs in Microsoft Jet Database Engine version 3.51, which is
available from the Microsoft Download Center.
For information on how to obtain Microsoft Jet Database Engine 3.51, please
see the following article in the Microsoft Knowledge Base:
Q172733 ACC97: Updated Version of Microsoft Jet 3.5 Available for Download
MORE INFORMATION
Steps to Reproduce Behavior
- Start Microsoft Access and create a new database called Db1.mdb.
- Create the following table in Design View:
Table: Table1
------------------
Field Name: Field1
Data Type: Text
Field Name: Field2
Data Type: Text
- On the Edit menu, click Select All.
- On the Edit menu, click Primary Key to create the multiple-field
primary key.
- Save the table as Table1.
- On the View menu, click Datasheet.
- Enter the following records:
Field1 Field2
------ ------
a a
b b
Close the table.
- Create the following table in Design view:
Table: Table2
------------------
Field Name: Field1
Data Type: Text
Field Name: Field2
Data Type: Text
Field Name: Field3
Data Type: Text
- Save the table as Table2 without defining any primary key.
- On the View menu, click Datasheet.
- Enter the following records:
Field1 Field2 Field3
------ ------ ------
a a x
b y
Do not enter any data in Field2 of the second record.
- Close the table.
- On the Tools menu, click Relationships. Add the Table1 table and the
Table2 table to the Relationships window, and then click Close.
- Select the Field1 field in the Table1 table and drag it to the Field1
field in the Table2 table.
- In the Relationships dialog box, click under the Field1 field in the
first column of the second row in the Table/Query grid; select Field2
from the list. Then click in the second column of the second row and
select Field2.
- Click to select the Enforce Referential Integrity check box, and then
click Create. Note that Microsoft Access allows you to create the
relationship, even though a record in the Table2 table contains a Null
value in one of the foreign key fields.
- Close the Relationships window and save the changes.
- Open the Table2 table in Datasheet view. Try to delete the data in
the Field2 field of the first record. Note that you receive one of the
errors described in the "Symptoms" section.
REFERENCES
For more information about referential integrity, ask the Microsoft Access
97 Office Assistant, or search the Help Index for "referential integrity"
and select "What is referential integrity?"
Additional query words:
composite compound
Keywords : RltRef
Version : 7.0 97
Platform : WINDOWS
Issue type : kbbug