There have been a few questions about how the main database submit data connection works with related tables. This is an FYI to clear up some of those issues, as I don't think this information ever made it public!
At least one of the relationships for every pair of related tables must include the left-hand table's primary key (PK) (where A is the left-hand table in "A relates to B on A.ID, B.ID"). Without this stipulation, there may be many records in table A that map to one record in table B along the defined relationship.
Note also that unique indexes and unique constraints allow nulls, so the primary key must be used in at least one relationship (they don't allow nulls). Basically, InfoPath enumerates the records to be updated according to the primary key. This way, the data inserted into the database is well-defined.
What that boils down to is this:
- Sufficient relationships:
- PK --> Non-unique, Unique, or PK
- Insufficient relationships (assuming a sufficient relationship hasn't already been defined):
- Non-Unique --> Non-unique, Unique, or PK
- Unique --> Non-unique, Unique, or PK
Once you've defined one sufficient relationship (as listed above), then you can define any other additional relationships that you want (they don't have to follow my rules listed above).
Software Development Engineer in Test