When setting up a replication group to refresh tables with foreign key relations, you need to order replications based on how foreign keys are created because a foreign key record cannot be inserted in the foreign key table before the corresponding key record is inserted in the key table. For example, with Employee and Department tables where a foreign key is defined for an Employee to a Department, you cannot insert an employee record if the employee's department does not exist. The referential integrity of the database does not allow that. During the refresh operation, Syniti Replicate should insert the departments first and then all the employees. However, when tables are deleted (during the truncation phase, prior to the insertion of records), the rule is reversed: a department record cannot be deleted if there is an employee record that has a reference to the department.
Using the example above, you would need to set up the refresh operation for the Department and Employee tables so that all the tables are first truncated in the reverse order to which they are refreshed:
1. Truncate Employee
2. Truncate Department
3. Insert records in Department
4. Insert records in Employee
However, the default order for truncation and subsequent insertion is as follows:
1. Truncate Table1
2. Insert records in Table1
3. Truncate Table2
4. Insert records in Table2
The Group Properties dialog in the Management Center provides a way for you to set up the correct truncation and insertion order:
Create a replication group for all the refresh replications on tables with foreign key relations.
Create each replication and add it to the replication group.
For each replication with one or more foreign keys, verify that the foreign key relation is visible in the Management Center Object Browser by selecting the table with the foreign key in the Metadata Explorer. Foreign key relations are displayed with an icon showing a grey key.
Note that if there is any error retrieving a foreign key, Syniti Replicate simply does not show the column as a foreign key.Verify that the foreign key relationship has been defined correctly in the target table, because that is where records need to be deleted in the correct order.
When you have created all replications in the group, in the Metadata Explorer, select the group.
From the right mouse button menu, choose Group Properties to open the Group Properties dialog.
On the Preferences tab, set the Truncate Inverse field to True.
This causes related tables to be truncated in the correct order to maintain data integrity.On the General tab, click Reorder Replications.
Syniti Replicate checks the foreign key relations and reorders the replications so that they will execute in an order that maintains data integrity at all times.
Related Topics
Adding a Replication to a Group