i have 2 tables that share a one to one relationship. the problem is that when i enter the data into a form, i get an "unable to add or change data in table1 because of related data in table2. it seems there is a "must have" relationship between the 2 tables, when i really need a "may have" relationship between the two. is this changed in the properties tab? how can i tell by looking at the relationships? oracle has a hashed mark connecting the two tables for "may have" and a solid line for "must have". my tables have a single solid line connecting them. thanks
table relationships
- Thread starter reyrey
- Start date
- Local time
- Today, 10:09
- Joined
- Feb 19, 2002
- Messages
- 46,915
The relationship is backwards. The wrong table is being designated as the "parent". Delete the relationship and recreate it with the other table being first.
Also, think long and hard about why you even have a 1-1 relationship. They are extremely rare in the real world and are used for two primary reasons.
1. Security - table 2 is restricted and the RDBMS doesn't allow you to do this field by field so you have to relagate the sensitive columns to a separate table.
2. Sparseness - in this case there will probably be several table2's. This structure is used when an entity can be multiple types and each type has different column requirements. For example, you might have employees, individual customers, and corporate customers. All have addresses and so need relationships to tblAddress. They also have different column requirements and so rather than have sparse (empty) columns for many records because customers don't have hire dates and employees don't have company names, you create separate tables for each type. To handle the other relationships, you create a "parent" table called Entity and use that to relate to tblAddress, etc. And the employee, individual, and corporate customer tables are all 1-1 with Entity.
The most common invalid reason is too many columns. The solution to this is to get rid of the repeating groups and normalize the schema.
Also, think long and hard about why you even have a 1-1 relationship. They are extremely rare in the real world and are used for two primary reasons.
1. Security - table 2 is restricted and the RDBMS doesn't allow you to do this field by field so you have to relagate the sensitive columns to a separate table.
2. Sparseness - in this case there will probably be several table2's. This structure is used when an entity can be multiple types and each type has different column requirements. For example, you might have employees, individual customers, and corporate customers. All have addresses and so need relationships to tblAddress. They also have different column requirements and so rather than have sparse (empty) columns for many records because customers don't have hire dates and employees don't have company names, you create separate tables for each type. To handle the other relationships, you create a "parent" table called Entity and use that to relate to tblAddress, etc. And the employee, individual, and corporate customer tables are all 1-1 with Entity.
The most common invalid reason is too many columns. The solution to this is to get rid of the repeating groups and normalize the schema.
spikepl
Eledittingent Beliped
- Local time
- Today, 16:09
- Joined
- Nov 3, 2010
- Messages
- 6,142
Doubleposted here: http://www.access-programmers.co.uk/forums/showthread.php?t=227730
Please do not doublepost
Please do not doublepost
