Options for Child Tables When Updating a Parent Table
When the mapping updates a table which is a "parent" table (that is, it has foreign key relationships to other tables), you can configure how the dependent records should be treated both in the source data and in the target table. For example, let's assume that you want to update the "Department" table in the Altova.sqlite database. Because every person is linked to a department by means of a foreign key, you will likely want to take action against the "Person" table as well (which could be an insert, update, or delete). Doing so would help you maintain the database integrity and avoid mapping errors.
This topic discusses the options available for the "Person" table when you update the parent "Department" table. It makes use of the following example files:
•altova-cmpy-extra.xml — contains the source data to be inserted into the database.
•Altova_Hierarchical.xsd — the schema used to validate the instance file above.
•Altova.sqlite — the target database to be updated.
All files are available in the <Documents>\Altova\MapForce2023\MapForceExamples\ folder. Below, the complete path to them will be omitted, for simplicity.
The mapping in this example modifies a sample database file. It is strongly recommended to back up the original database and start with a new copy before following the steps below. This ensures that the original examples are not overridden and that you get the same results as below. For more information, see Executing Mappings Which Modify Databases. |
First, add the source XML schema and instance as well as the target database to the mapping. Follow the same steps as in "Update if... Insert Rest" Action. Secondly, draw the mapping connections as shown below:
As illustrated above, the mapping updates the "Department" table in the target database. The "Department" table is chosen as "root" table. For more information about what a root table is and why it is necessary, see Handling Database Relationships. The action to be taken against the child "Person" table is the subject of this topic.
The following tables illustrate various configuration options and the corresponding mapping result. These options can be selected from the Database Table Actions dialog box of the parent "Department" table and the child "Person" table.
Configuration A
Settings | Mapping result |
|---|---|
 "Department" table  "Person" table | •Updates Department records where PrimaryKey in the source XML corresponds to the PrimaryKey in the database table. •Does not update existing Department records which do not have a counterpart in the input XML file (no such key exists in the source). •Inserts, from the input XML instance, all Person records that do not already exist in the database. •Deletes child data (Person records) of those Department records which satisfy the Update if... condition. |
Configuration B
Settings | Mapping result |
|---|---|
 "Department" table "Person" table | The mapping fails with an SQL execution error. The reason is that the mapping attempts to insert new Person records with the same primary key as the existing Person records. If you want to insert records from the input XML in addition to those already in the database, see the next option. |
Configuration C
Settings | Mapping result |
|---|---|
"Department" table  "Person" table | •Updates Department records where PrimaryKey in the source XML corresponds to the PrimaryKey in the database table. •Does not update existing Department records which do not have a counterpart in the input XML file (no such key exists in the source). •New Person records (with generated primary keys) are inserted into the Person table in addition to existing ones. |
Configuration D
Settings | Mapping result |
|---|---|
 "Department" table "Person" table | •Updates Department records where PrimaryKey in the source XML corresponds to the PrimaryKey in the database table. •Does not update existing Department records which do not have a counterpart in the input XML file (no such key exists in the source). •No records are inserted in the Person table because the option Ignore input child data is enabled for the parent Departments table. |
Configuration E
Settings | Mapping result |
|---|---|
"Department" table  "Person" table | •Updates Department records where PrimaryKey in the source XML corresponds to the PrimaryKey in the database table. •Does not update existing Department records which do not have a counterpart in the input XML file (no such key exists in the source). •Deletes all Person records linked to a Department which has a corresponding PrimaryKey in the source XML. The reason is that the Delete data in child tables option is enabled for the parent Department table. •Person records linked to a department that did not meet the Update if... condition remain in the database. •No records in the Person table are updated. |
Configuration F
Settings | Mapping result |
|---|---|
"Department" table  "Person" table | •Updates Department records where PrimaryKey in the source XML corresponds to the PrimaryKey in the database table. •Does not update existing Department records which do not have a counterpart in the input XML file (no such key exists in the source). •Deletes all Person records linked to a Department which has a corresponding PrimaryKey in the source XML. The reason is that the Delete data in child tables option is enabled for the parent Department table. •Person records linked to a department that did not meet the Update if... condition remain in the database. |
Configuration G
Settings | Mapping result |
|---|---|
"Department" table "Person" table | •Updates Department records where PrimaryKey in the source XML corresponds to the PrimaryKey in the database table. •Does not update existing Department records which do not have a counterpart in the input XML file (no such key exists in the source). •Deletes all Person records which satisfy both of the following conditions:
a.The Person record is linked to a Department which has a corresponding PrimaryKey in the source XML, and b.The Person record has a corresponding PrimaryKey in the source XML. |