Knowledge Base

ACC: Cannot Create 1:1 Relationship Without Unique Index

Article ID: 141106

Article Last Modified on 1/19/2007

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.

MORE INFORMATION

Steps to Reproduce Behavior

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

REFERENCES

For more information about creating relationships, search the Help Index for "relationships," or ask the Microsoft Access 97 Office Assistant.

Keywords: kbprb KB141106