Microsoft KB Archive/208660

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

Article ID: 208660

Article Last Modified on 7/16/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q208660



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 Edit Relationships dialog box.



CAUSE
The "foreign key" table does not have a unique index. In Microsoft Access 2000, 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.



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) Close and save the Shippers2 table.
 * 6) On the Tools menu, click Relationships.
 * 7) On the Relationships menu, click Show Table. Select Shippers2. Click Add, and then click Close.
 * 8) In the Relationships window, create a relationship from the ShipperID field in the Shippers table to the ShipperID field in the Shippers2 table.
 * 9) Click Enforce Referential Integrity. Note that the only available Relationship type is One-to-Many.

