Microsoft KB Archive/209129

= ACC2000: Cannot Enforce Referential Integrity in Relationship =

Article ID: 209129

Article Last Modified on 1/26/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209129





SUMMARY
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 a linked 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.
 * 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.

