ACC: Cannot Create 1:1 Relationship Without Unique Index

• Microsoft Access versions 7.0, 97

SYMPTOMS

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

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

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.

REFERENCES