You can read data from multiple source objects and integrate it in the target object based on the primary key and foreign key relationship. If you link multiple source objects to a target transformation, you must define primary and foreign keys for the fields in the source objects. You can then link these fields to appropriate target fields to update or insert target data.
Each field group within the target fields has a unique sourcePKey field and a parentId field. After you designate primary keys and foreign keys to input fields, you can map them to the sourcePKey and parentId fields within the appropriate field group in target fields. If multiple field groups are nested within a field group, the parentId field indicates the position of the field group in the hierarchical structure of target field groups. The parentId field of a nested field group must be the sourcePKey field of its parent field group.
Ensure that the sourcePkey field contains only alphanumeric characters and the following special characters:
.~!/'={}|:@#$^&*()-_+,<>?`
Otherwise, the target transformation fails. If required, correct the source primary key values in the source objects.
The following image shows a sample configuration of primary and foreign key fields linked to the target fields:
Note: You cannot designate the primary key and foreign key if you use the hierarchy as the target object. Don't map multiple source objects to a hierarchy in the target. If you map fields from multiple source objects to a hierarchy in the target, you get an error on the Field Mapping tab.
Selecting a primary or foreign key
Assign the primary keys and foreign keys for the input fields and map them to the appropriate target fields.
1In the Properties panel of the target transformation, click the Field Mapping tab.
The field mapping editor displays the input fields to the left and the target fields to the right.
2To designate an input field as a primary or foreign key, click the Key column of the field.
The Mark as Key window appears.
3 Select whether the key is a primary key or foreign key.
4If the field is a foreign key, select the group to which the field relates as a foreign key.
5To remove a primary or foreign key, click the Key column of the field and select Not a key.
6To clear all the keys, click the action menu at the top of the Target Fields panel and then select Clear Keys.
