ACC: Cannot Enforce Referential Integrity in Relationship

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

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

You cannot enforce referential integrity when you are defining a relationship between tables.

CAUSE

There are several reasons why you may not be able to enforce referential integrity when you define a relationship, including the following:

• Existing data in the tables violates referential integrity. For example, you have a child record that does not have a matching parent in the primary table.
• You can define a relationship with an attached table, but Microsoft Access does not enforce referential integrity between two tables unless both tables are in the same database and the database user has permissions to create the relationship in that database.
• In Microsoft Access 2.0, you started dragging from the wrong table or field. You must drag the primary key field (or uniquely indexed field) from the primary table to the related table. For example, to enforce referential integrity between the Customers and Orders tables in the sample database NWIND.MDB, you must drag the Customer ID field (primary key) from the Customers table to the Customer ID field in the Orders table. If you did not start dragging from the primary table, click Cancel in the Relationships dialog box and start again.
• You chose the correct fields, but they are not appropriately indexed. Before adding relationships, open the primary table in Design view and set a primary key or create a unique index for the fields you want to use in an enforced relationship.
• Although you can create relationships using queries as well as tables, referential integrity is not enforced with queries.

MORE INFORMATION

Referential integrity is a set of rules that preserves the defined relationships between tables when you enter or delete records. If you enforce referential integrity, you cannot add a record to a related table when there is no associated record in the primary table, change a value in a primary table that would result in an orphan record in a related table, or delete a record from a primary table when there is a matching related record.

If you select the Cascade Update Related Fields or Cascade Delete Related Records option for a relationship, Microsoft Access will change or delete related records to ensure that the referential integrity rules are enforced when you change or delete records.

REFERENCES

For more information about referential integrity, type "referential integrity" in the Office Assistant, click Search, and then click to view "What is referential integrity."