Roger Reinsch
Registered User.
- Local time
- Today, 08:38
- Joined
- Sep 3, 2012
- Messages
- 18
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.
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.
