ACC2: Error When Trying to Enforce Referential Integrity

• Microsoft Access version 2.0

SYMPTOMS

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

When you create a relationship in the Relationships window by dragging a field from one table to another, you get the following error message when you select "Enforce Referential Integrity" and choose the Create button:

   Can't create relationship to enforce referential integrity. The
   field(s) in the primary table must be the primary key or have a unique
   index.

CAUSE

You must drag the joining field from the "one" side to the "many" side in order to enforce referential integrity." The field on the "one" side must also be the primary key field in that table, or have a unique index. If you are dragging the joining field from the "one" side to the "many" side and still get this error, another possible cause might be the way your indexes are set up.

RESOLUTION

To set up your indexes correctly, open the "one-side" table in Design view and set a primary key, or create a unique index, for the field you want to use. Then, create the relationship between the tables.

The following example uses the sample database NWIND.MDB to demonstrate a relationship with referential integrity enforced:

1. Open the sample database NWIND.MDB.

2. From the Edit menu, choose Relationships.

3. From the Edit menu, choose Clear Layout. When you are prompted to continue, choose OK.

4. From the Relationships menu, choose Add Table.

5. Double click the Products table and the Order Details table. Choose Close.

6. If there is already a relationship line drawn between the Products table and the Order Details table, select it and press DEL to remove it. When you are prompted to continue, choose OK.

7. Drag the Product ID field from the Products table onto the Product ID field in the Order Details table.

8. In the Relationships dialog box, select the "Enforce Referential Integrity" check box, and then choose Create.

   This will create a one-to-many relationship between the Products table and the Order Details table.

9. Close the Relationships window. Do not save your changes.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database NWIND.MDB.

2. From the Edit menu, choose Relationships.

3. From the Edit menu, choose Clear Layout. When you are prompted to continue, choose OK.

4. From the Relationships menu, choose Add Table.

5. Double click the Products table and the Order Details table. Choose Close.

6. If there is already a relationship line drawn between the Products table and the Order Details table, select it and press DEL to remove it. When you are prompted to continue, choose OK.

7. Drag the Product ID field from the Order Details table onto the Product ID field in the Product table.

8. In the Relationships dialog box, select the "Enforce Referential Integrity" check box, and then choose Create.

   This will cause the error message to pop up. You must drag the field from the "one" side (the Products table) to the "many" side (the Order Details table) in order to enforce referential integrity.

9. Close the Relationships window. Do not save your changes.

REFERENCES

For additional information about setting up relationships, search for "Relationships" using the Microsoft Access Help menu.

Microsoft Access "User's Guide," version 2.0, Chapter 2, "Designing a Database (Determining the Relationships)"