ms-accessouter-joinreferential-integrity

Is referential integrity commutative (i.e., is it direction-dependent)?


I've had some behavior in MS Access 2019 that surprised me and I've boiled it down to the following:

I've got two tables with a different number of records:

enter image description here

I need to establish an outer join between them that includes all records from table [test 1] and only those records of [test 2] where the joined fields are equal, and I'd like to have referential integrity so that I can't accidentally delete or modify a joined field on one side only.

When I open the Relationships window with the two tables and drag field [ID] from table [test 1] to [test 2], the join properties I need appear as choice 2:

enter image description here

When I attempt to create the join, I get an error message saying that data in table [test 2] violates referential integrity:

enter image description here

However, if I define the join in the opposite direction by dragging field [ID] from table [test 2] to [test 1], the result is different. First, the join properties I need appear as choice 3:

enter image description here

I've seen that difference before, and it's no problem. But the surprise is that when I attempt to create the join, now it works:

enter image description here

So my ability to establish referential integrity appears to depend on which direction I drag the field to set up the join. (Does that make the join left vs. right?) I don't remember seeing anything before about directional dependence (or perhaps it could be called non-commutativity) of referential integrity. The purpose of referential integrity is to prevent me from deleting or modifying a joined field in one table without making the corresponding change in the other. How does that objective depend on which direction I drag the field to set up the join?


Solution

  • Short Answer. No, referential integrity is not commutative.

    Column X references column Y is not the same as column Y references column X.

    Deep dive. The idea of foreign keys is fundamental to the Relational model of data. Without it, the expressive power of the model would be so hampered that it would never have caught on the way it did some 50 years ago. A foreign key can be a foreign key with or without a foreign key constraint. Still, the foreign key constraint will usually be helpful, for reasons you mention in your question.

    And you are right that dragging from X to Y won't produce the same constraint as dragging Y to X.

    Outer joins nearly always produce all the results from the referenced side, and only the valid results from the referencing side. Outer joins and inner joins can both be useful, in different circumstances. That is why Access offers you three options.