Fix Microsoft Access Referential Integrity Cannot Be Enforced
Question details
The user needs to enforce referential integrity in Microsoft Access but is blocked by unmatched foreign-key values in the child table.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- The user is trying to create a relationship between a parent table and a child table, such as Customers and Orders, and enforce referential integrity.
- Observed behavior
- Access prevents the relationship and shows a referential integrity error because the child table contains orphan foreign-key values, including CustomerId equal to zero.
Open the database in Microsoft Access and make sure it is in a trusted location so relationship and design changes are allowed. Identify the parent table, child table, and the exact fields used in the relationship before editing records.
Locate and Correct Unmatched Foreign-Key Values in the Child Table
Use this solution when Access refuses referential integrity because the child table contains orphan records or foreign-key values such as 0 that do not exist in the parent table.
Access enforces referential integrity only when every value in the foreign-key field matches a primary key in the parent table. Orphan values such as 0, blank, or IDs that no longer exist block the relationship.
In the Microsoft Access Navigation Pane, double-click the child table, for example Orders, to open it in Datasheet View.
Locate the column that stores the parent key, such as CustomerId, and check for values that do not appear in the parent table's primary key field.
For each unmatched value, type a valid CustomerId from the Customers table, or delete the record if it is no longer needed.
Close the child table, reopen the relationship editor, and try enforcing referential integrity again.

Clear Default Value Properties for Foreign-Key Fields
Use this solution to stop new records from automatically entering zero in CustomerId, ProductId, or other foreign-key fields.
Use WPS Office as a Free Microsoft Office Alternative for Daily Document Work
Microsoft Access remains the correct tool for .accdb and .mdb database relationships, but WPS Office is a free, lightweight alternative for the daily Word, Excel, and PowerPoint work around that database. It supports Microsoft Office formats, offers a familiar ribbon interface, and helps you migrate documents without losing formatting.

Frequently Asked Questions
Why does Access say referential integrity cannot be enforced?
Access prevents referential integrity when the child table contains foreign-key values that do not match any primary key in the parent table, such as CustomerId equal to 0. Open the child table and correct or delete those unmatched records, then try again.
How can I quickly find unmatched CustomerId values in Orders?
In Microsoft Access, create a query in Design View, add both the Orders child table and Customers parent table, join CustomerId to the parent primary key, and set the parent key criteria to Is Null. This returns the orphan child records.
Should I clear the Default Value on a foreign-key field?
Yes. Open the child table in Design View, select the foreign-key field such as CustomerId or ProductId, and delete the Default Value property. This prevents new records from automatically being assigned zero.
Can WPS Office open an Access database to fix this issue?
No. WPS Office does not edit .accdb or .mdb database files. Use Microsoft Access for database relationship tasks, and use WPS Office as a free alternative for Word, Excel, PowerPoint, and PDF document work.




