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

Harold E1 25 Reputation points
2026-09-27T01:47:08.3433333+00:00

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

Microsoft 365 and Office | Access | For home | Windows
Answer recommended by moderator
Harold E1 25 Reputation points
2026-09-30T02:00:05.5133333+00:00

it turns out that I did indeed have a child record with an ID not in the parent table.

it turns out that I did indeed have a child record with an ID not in the parent table.

I THOUGHT I had done an outer join to double check, but if I did, I did it incorrectly.

Proving once again that Will Rogers was correct when he said it’s not what we don’t know, but what we know that ain’t so that gets us in trouble!😁

Was this answer helpful?

1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Teddie Dang 1,365 Reputation points Independent Advisor
    2026-09-27T02:30:57.19+00:00

    Hi @Harold E1

    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.

    Was this answer helpful?

    1 person found this answer helpful.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.