ACC2000: Cannot Create 1:1 Relationship Without Unique Index (208660)


The information in this article applies to:

  • Microsoft Access 2000
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.

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

REFERENCES

For more information about creating relationships, click Microsoft Access Help on the Help menu, type create or modify relationships in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:7/16/2004
Keywords:kbprb KB208660
©2004 Microsoft Corporation. All rights reserved.