-1

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?

NewSites
  • 1,402
  • 2
  • 11
  • 26
  • 1
    Querying does not depend on constraints. When constraints hold, some expressions return the same thing as others that wouldn't otherwise, so more queries return a desired result that wouldn't otherwise. A "referential integrity" constraint includes that subrow values appear elsewhere. That doesn't commute; there could be more values elsewhere. x left join y on c is y right join x on c; it is not y left join on c. "outer join between them" "and I'd like to have referential integrity" It's not clear what you want, you mix querying/joining with constraints holding. Please give a [mre] in text. – philipxy Mar 20 '21 at 00:07
  • Definitely can't set referential integrity by linking two autonumber fields. Relationship links must be on PK and FK fields and FK cannot be autonumber. Referential integrity is used when you want to, for example, prevent an order for a customer that is not in Customers table. Even if a relationship is defined, nothing prevents building a query with different JOIN. Regardless, linking on autonumber fields can never be reasonable. – June7 Mar 20 '21 at 00:18
  • The only common data in your tables that a link can be built on are the `data` fields. If Test1 is the 'parent' or master (or lookup table) then Test2 fails integrity because of record "D". Test2 can be the parent and Test1 the dependent table. – June7 Mar 20 '21 at 00:27
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. – philipxy Mar 20 '21 at 00:52
  • Please clearly explain what you want to do to what to achieve what & the exact series of clicks you are doing on what starting from what state because joining & setting up FKs are two separate things. If you're trying to follow a reference, please identify & quote it. Otherwise, say why you are doing these things for that goal. – philipxy Mar 20 '21 at 05:42

1 Answers1

1

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.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58