Why am I getting foreign key errors during replication?

There are a few ways you could receive constraint errors during replication. Below are some of the most common cases and solutions to avoid them.

Foreign Key Issues Causes and Solutions

1.  The source and target are not in sync when replication begins.

• SOLUTION:Run an initial load or use an external process to backup and restore from source to target.

2.  There are dependent tables in the source that are not setup for replication.  For example the child table is configured but not the parent and an insert to both would only sync the child row.

• SOLUTION: Configure all dependent tables to be part of replication.

3.  Transforms manipulate the data in a way that performs additional inserts (not common)

• SOLUTION: Verify any transforms in place are not running additional SQL to change dependent tables on the target or that they are not writing to more tables on the target.

4.  The constraints on the source and target are different.

• SOLUTION: Alter the target to match all constraints for tables that are being replicated.

5.  Tables are configured in different channels.   Channels can sync independently so its important to make sure all dependent tables are in the same channel.

• SOLUTION: Adjust table trigger configurations so that all dependent tables are in the same channel.

 Auto Resolve

SymmetricDS will by default automatically try to resolve any foreign key issues as long as all dependent tables are configured for replication and the issue is not on a reload channel.   This is controlled by the following parameter which defaults to true.

auto.resolve.foreign.key.violation=true