Cannot enforce referential integrity, even though there are NO records in the child table without an ID in the parent table

Question

Harold E1 25

Cannot enforce referential integrity, even though there are NO records in the child table without an ID in the parent table.

1 answer

Answer 1

Teddie Dang 1,365 Independent Advisor

In addition to checking for orphaned records, verify the following:

The related fields have compatible data types. If the parent field is an AutoNumber, the corresponding child field should normally be a Number field with Field Size = Long Integer.
The parent field is a primary key or has a unique index. The field on the "one" side of the relationship must uniquely identify each parent record.
The relationship uses the intended fields. Make sure you are relating the correct fields, for example, ParentTable.ID > ChildTable.ParentID, rather than another field containing similar values.
There are no duplicate values in the parent field. If the parent field is not the primary key, it must have a unique index for Access to use it on the "one" side of the relationship.
Null values are handled as expected. A Null value in a child foreign-key field is not considered an orphan. However, every non-null foreign-key value must have a matching parent record.
The tables support enforced relationships. If the tables are linked, read-only, or stored in a separate back-end database, this can affect where referential integrity can be enforced. For a split Access database, for example, the relationship should be created in the back-end database where the tables physically reside.

Harold E1 25 Reputation points

2026-09-27T22:17:07.67+00:00
Both fields are the primary keys in the tables and our order number. So they are of the same type and unique.

Yes, I have double checked – I'm joining the correct fields.

There are no Null values.

The tables are not linked, read-only or stored in a separate database.

I can join the tables in Relationships. I just can't enforce referential integrity
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Teddie Dang 1,365 Reputation points Independent Advisor

2026-09-29T04:27:49.81+00:00

Hi @Harold E1

Thank you for confirming those details.

Since the relationship can be created and the usual data and table conditions appear to be satisfied, could you provide the exact error message Access displays when you try to select Enforce Referential Integrity?

Also, when you say both fields are the same type, could you provide their exact Data Type and Field Size as shown in Table Design View? This will help confirm whether the field definitions match exactly.