Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 208353 - Last Review: June 29, 2004 - Revision: 2.0
ACC2000: Can't Create Relationship with Multiple-Field Primary Key
This article was previously published under Q208353
Novice: Requires knowledge of the user interface on single-user computers.
When you try to create a relationship that enforces referential integrity,
and the relationship involves a multiple-field primary key, you may receive
the following error:
No unique index found for referenced field of primary table.
The order of the primary key fields in Design view of the table is
different from the order of the fields in the PrimaryKey index.
There are two ways to work around this error:
- Reorder the PrimaryKey fields in the index so that they appear in the same order as they do in Design view of the table.
-or-
- Order the field names in the Relationships dialog box to match the order of the field names in the PrimaryKey index.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
Steps to Reproduce Problem
Creating the Tables
- Create a new table in Design view.
- Add the following fields to the table:
Table:
------------------
Field Name: A
Data Type: Text
Field Name: B
Data Type: Text
- Highlight both fields.
- On the Edit menu, click Primary Key.
- On the View menu, click Indexes.
- Change the order of the fields in the PrimaryKey index so that B is in the first row, and A is in the second row:
Index Name Field Name Sort Order
------------------------------------
PrimaryKey B Ascending
A Ascending
- Close the Indexes dialog box.
- Save the table as tblOne and close it.
- Repeat steps 1 and 2.
- Save the table as tblTwo and close it. You do not need to create a
primary key index on this table.
Creating the Relationships
- Open the Relationships window by clicking Relationships on the Tools menu.
- On the Relationships menu, click Show Table. Add the tblOne table and the tblTwo table to the Relationships window.
- Highlight fields A and B in the tblOne table by holding down the SHIFT key while you click both fields, and then release the SHIFT key.
- Drag the highlighted fields A and B from the tblOne table to the tblTwo table to open the Relationships dialog box.
- Select fields A and B from the tblTwo table in the Related Table/Query column, so that the Relationships dialog box looks as follows:
Table/Query: tblOne Related Table/Query: tblTwo
-------------------------------------------------
A A
B B
- Click Enforce Referential Integrity.
- Click the Create button. Note that the error message appears.
- Click Cancel in the Relationships dialog box, and then close the Relationships window. You do not need to save your changes.
For additional information about using a multiple field primary key, click the article number below
to view the article in the Microsoft Knowledge Base:
209564Â
(http://kbalertz.com/Feedback.aspx?kbNumber=209564/EN-US/
)
CC2000: Compound Indexes Must Restrict First Indexed Field
For more information about referential 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
| kberrmsg kbbug kbnofix KB208353 |
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