Microsoft KB Archive/141106

= ACC: Cannot Create 1:1 Relationship Without Unique Index =

Article ID: 141106

Article Last Modified on 1/19/2007

-

APPLIES TO


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

-



This article was previously published under Q141106



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



SYMPTOMS
When you try to create a one-to-one relationship between two tables and enforce referential integrity, the one-to-one relationship type is not available in the Relationships dialog box.



CAUSE
The "foreign key" table does not have a unique index. In Microsoft Access 97 and 7.0, the Microsoft Jet database engine requires that you create a unique index on the foreign key table before creating a one-to-one relationship that enforces referential integrity. However, in Microsoft Access version 2.0, the Microsoft Jet database engine automatically creates a unique index on the "foreign key-side" if one was not already defined.



RESOLUTION
Add a unique index to the foreign key table, and then create the one-to-one relationship.



Steps to Reproduce Behavior

 * 1) Open the sample database Northwind.mdb.
 * 2) Make a copy of the Shippers table and name it Shippers2.
 * 3) Open the Shippers2 table in Design view.
 * 4) Select the ShipperID field and click the PrimaryKey icon on the toolbar to remove the primary key.
 * 5) Change the Indexed property of the ShipperID field to No.
 * 6) Close and save the Shippers2 table.
 * 7) On the Tools menu, click Relationships.
 * 8) On the Relationships menu, click Show Table. Select Shippers2. Click the Add button, and then click the Close button.
 * 9) In the Relationships box, create a relationship from the ShipperID field in the Shippers table to the ShipperID field in the Shippers2 table.
 * 10) Click Enforce Referential Integrity. Note that the only available Relationship type is One-to-Many.

