ACC2000: Cannot Enforce Referential Integrity in Relationship (209129)

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.

REFERENCES

For more information about referential integrity, click Microsoft Access Help on the Help menu, type What is referential integrity? in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.