One-to-Many Relationships on Database Connections

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).
- Forrest
Software Development Engineer in Test
Comments (9)

  1. says:

    I don’t completely understand this. Suppose you have these tables:

    Person (PK PersonName)

    TraveledTo (PersonName, CountryName) => PK spans all

    Country (PK CountryName, CountryPopulation)

    Suppose you want to create a form to add/remove people and specify the countries they have visited, with a main datasource that includes the above tables in the sequence shown. It doesn’t seem possible (or easy) to allow the user to add new countries with this form and I’m not sure why there is this technical limitation. The form can only reference existing countries on the fly – a different form is needed to add them. Why can’t InfoPath be smart and know to insert a new Country into the database if details about it, like CountryPopulation, are specified? If no details are specified, then InfoPath can assume an existing County is being referenced.

  2. says:

    It also seems odd (inconsistent) that the rules for Insert are different than the rules for Update. In the example above, if it has been recorded that…

    Person Bob

       traveled to Country India

            has Population 1,000,000

    …I am able to update India’s population from InfoPath even though the relationship from TraveledTo to Country does not have a PK on the left side. Why can I update the Country table but not insert a new tuple in it?

  3. forrestd says:

    Thanks for your reply and your interest, justinm!

    I am going to re-state your questions as I interpret them:

    1) When I add the "Countries" table to my data source, and relate the "TraveledTo" table to the "Countries" table on the "CountryName" columns in each table, why doesn’t InfoPath infer the schema such that "Countries" is a repeating node?

    2. Why does InfoPath tell me I may have a many-to-one relationship when I’ve followed the "Primary Key on the left at least once in every relationship between two tables?"

    Let’s start with #1:

    The "Countries" table has a primary key constraint defined on the "CountryName" column.  In the data source, the "TraveledTo" table is related to the "Countries" table on the "CountryName" columns in each table.  This relationship requests, "for each record in the ‘TraveledTo’ table, fetch the record in the ‘Countries’ table that has an identical ‘CountryName’ value."  But, since "Countries"."CountryName" is a primary (unique) key, there will always exist 0 or 1 record(s) that satisfy the relationship.  As a result, InfoPath infers a schema that reflects the fact that the query may return no more than one record from the "Countries" table in the context of the relationship.  Therefore, the node is non-repeating, and you can’t add more countries.

    In the context of the relationship between tables, the country name is already determined by the value of the "TraveledTo" record.  Therefore, the relationship indicates that any countries that would be added would have the same country name.  But such an addition would violate the primary key constraint of the "Countries" table.  If, however, you remove the unique constraint from the "Countries"."CountryName" column, then InfoPath will infer a repeating schema node, and you can add new countries, because no constraint will be violated.  It’s not so much a matter of being smart about manipulating the data source, but rather about adhering to standards that provide structure for InfoPath’s interaction with the database.

    Now for #2:

    When you define a multiple-column primary key, no subset of the primary key is guaranteed to be unique.  In the case of the "TraveledTo" table, each of the "PersonName" and "CountryName" columns can have duplicate values.  As a result, when you define a relationship between the "TraveledTo" table and another table via the "CountryName" column, there may be many records in the "TraveledTo" table that map to one record in the related table.

    When such a many-to-one relationship is present, querying the data source will result in an individual record being displayed and editable in the form in more than one section at once.  If the user edits the record in multiple places in the form, conflicts must then be resolved at submit time.

    – Forrest

    Software Development Engineer in Test

  4. infopath says:

    If you’re interested in making InfoPath forms work with highly relational data through ADO.NET datasets, be sure to check out this article by Michael Hofer.

    – Alex

  5. says:

    Question.  I am just looking to see if this is the right tool for me.  I am looking to do no programming and have users INSERT answers to a questionaire.  so I would have a question table, an answers table, and a dropdown table that would give a selection of possible answers for each question.  Only the answers table would grow.  I could quickly code this in VB6 without issue using SPs but could this tool do this out of the box?  do you have any examples of this being done?  I know of no way to do this with your tool without going into code. how much code would this require

  6. Michelle Lee says:

    Nice post. Here’s a tutorial that shows how you can easily build an online database-driven web application with a parent-child table relationship, without…/creating-one-to-many-relational-datapages

Skip to main content