Constraint based loading technique is available in Informatica PowerCenter since couple of its prior versions. This PowerCenter feature will let you load multiple tables in a single session, which is having database level primary key - foreign key constraint or parent - child relation. In the Workflow Manager, you can specify constraint-based loading for a session. When you select this option, the Integration Service orders the target load on a row-by-row basis. For every row, the Integration Service loads the row first to the primary key table, then to any foreign key tables. Here are couple of rules for setting up Constraint-based loading in a particular session and lets see those in detail.
Target Connection Groups.
Treat Rows as Insert.
1. Key Relationships.
When target tables have no key relationships, the Integration Service does not perform constraint-based loading. Similarly, when target tables have circular key relationships, the Integration Service reverts to a normal load. It reverts to a normal load. When the target definition is imported into the Target Designer, PowerCenter will detect the Key Relationship defined on the database.
2. Active Source.
Both the parent and child table need to be mapped from a single active source such as Source Qualifier, Aggregator, Normalizer etc. When target tables receive rows from different active sources, the Integration Service reverts to normal loading for those tables. 3. Target Connection Groups.
The Integration Service enforces constraint-based loading for targets in the same target connection group. If the tables with the primary key-foreign key relationship are in different target connection groups, the Integration Service cannot enforce constraint-based loading when you run the workflow.
To verify that all targets are in the same target connection group, complete the following tasks. All targets are in the same target load order group and receive data from the same active source. Use the default partition properties and do not add partitions or partition points. Define the same target type for all targets in the session properties. Define the same database connection name for all targets in the session properties. Choose normal mode for the target load type for all targets in the session properties.
4. Treat Rows as Insert.
Use constraint-based loading when the session option Treat Source Rows As is set to Insert. You might get inconsistent data if you select a different Treat Source Rows As option and you configure the session for constraint-based loading