Nulls and One to One relationships (1 Viewer)

In the simple case I have three tables.
The first has 10 rows with fruits as a column as the Primary Key.
The second has 5 rows with fruitsLiked as the primary key and foreign key to fruits.
The third has 7 rows with fruitsHated with an index with no duplicates allowed on fruitsHated which is also a foreign key to fruits.

All 5 rows of the second table have a non-null value for fruitsLiked. With the database tools relationships view I can build a one-to-one relationship with inner join and referential integrity and cascaded updates between the first two tables. No problem.

2 of the 7 rows in the third table have <null> in the fruitsHated column. This causes no difficulty for the Unique Index! But I can't create the table relationship between the first and third tables. None of the Join Types succeed (Inner, Left Outer, Right Outer) as the "data doesn't meet the referential integrity rules".

Are the Nulls what are causing the problem? Is it OK to not have a row in the second table but it's not OK to have a row with a Null for the foreign key (non-value)? I can build the relationships in a query but would prefer to have it built into the data table level. Any advice is welcomed.

Let me start at the end. Queries don't define relationships. Those can only be defined using the relationship window. Relationships are used to enforce referential integrity and cascade update and delete as appropriate. Keep in mind that cascade update ONLY propagates changes in a PK to FKs in tables defined to have a relationship with each other. Cascade update is EXTREMELY rare in a properly defined schema since most tables will use autonumbers as their primary keys. Since you can't change the autonumber of an existing record, there is no update to cascade and you would never choose that option. The ONLY time you would ever use cascade update is in a case where you had a "natural" key that could be changed. Very few applications allow that also so I can tell you that I probably have not used Cascade Update even a dozen times in 40 years of working with relational databases.

The lines drawn in the QBE tell the query engine which columns to join on. This has nothing to do with relationships although, joins are almost always done along relationship lines when they exist because they make sense. For example if you have tblA related to tblB and tblA related to tblC. You will define relationships from A to B and from A to C. B and C have FKs that point to the same record in A but that doesn't mean that it makes sense to join B to C and in fact, most times, the query returns nonsense. A simplistic example is people have autos and people have pets. That doesn't mean that it makes sense to join pets to autos on their PeopleFK.

Now we get to 1-1 relationships. Again, these are the rarest of the rare. There are very few applications that ever have a 1-1 relationship and the ones that are created by new developers are generally created for the wrong reason. A couple of valid reasons:
1. Extra security - you want to use the database engine to lock data rather than trying to control access to it via forms.
2. Isolate memo fields - if memo or other very large text fields are rarely included in queries, they can be isolated in a separate table to ease the load on the database engine when processing the main table.
3. To implement complex relationships where each entity type has different data requirements.

To get to your question. When you join three tables a-->b-->c, you can only get to c if there is a record in b that relates to a.

Your post gives no clue what you are really asking. It is obviously a made up example and I can't tell if you are actually trying to model a real-world relationship or simply playing to expand your knowledge.

If you have something you need to model, please tell us what it is.