ACC2000: Cannot Create 1:1 Relationship Without Unique Index

• Microsoft Access 2000

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.

Additional query words: prb

Keywords          : kbdta 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: May 13, 1999