Partager via

One-to-Many Relationships on Database Connections

  • Article

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

  • Anonymous
    October 26, 2006
    The comment has been removed
  • Anonymous
    October 26, 2006
    The comment has been removed
  • Anonymous
    October 27, 2006
    The comment has been removed
  • Anonymous
    November 22, 2006
    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