Microsoft KB Archive/155514

= ACC: Cannot Create Relationship with Multiple-Field Primary Key =

Article ID: 155514

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q155514



Novice: Requires knowledge of the user interface on single-user computers.



SYMPTOMS
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.

In Microsoft Access 7.0 and 97

--

No unique index found for referenced field of primary table.

In Microsoft Access 2.0

---

Can't create relationship to enforce referential integrity. The field(s) in the primary table must be the primary key or have a unique index.



CAUSE
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.



RESOLUTION
There are two ways to work around this error:


 * 1) Reorder the PrimaryKey fields in the index so that they appear in the same order as they do in Design view of the table.
 * 2) Order the field names in the Relationships dialog box to match the order of the field names in the PrimaryKey index.



STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 2.0, 7.0, and 97.



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 by holding down the SHIFT key while you click the row selector button to the left of each field name, and then release the SHIFT key. On the Edit menu, click Primary Key (or Set Primary Key in version 2.0).</li> On the View menu, click Indexes.</li>  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:

<pre class="fixed_text">      Index Name   Field Name   Sort Order PrimaryKey  B            Ascending A           Ascending </li> Close the Indexes dialog box.</li> Save the table as tblOne and close it.</li> Repeat steps 1 and 2.</li> Save the table as tblTwo and close it. You do not need to create a primary key index on this table.</li></ol>

Creating the Relationships
<ol> Open the Relationships window by clicking Relationships on the Tools menu (or in version 2.0, click Relationships on the Edit menu.)</li> On the Relationships menu, click Show Table (or Add Table in version 2.0). Add the tblOne table and the tblTwo table to the Relationships window.</li> 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.</li> Drag the highlighted fields A and B from the tblOne table to the tblTwo table to invoke the Relationships dialog box.</li>  Select fields A and B from the tblTwo table in the Related Table/Query column, so that the Relationships dialog box looks as follows:

<pre class="fixed_text">     Table/Query: tblOne   Related Table/Query: tblTwo -              A                       A               B                       B                        </li> Click Enforce Referential Integrity.</li> Click the Create button. Note that the error message appears.</li> Click Cancel in the Relationships dialog box, and then close the Relationships window. You do not need to save your changes.</li></ol>

<div class="references_section">