If you don't configure the load order to load parent tables first, then a child table might load before its parent table. This causes the task to fail with a foreign key constraint violations error. In this case, you see log entries that are similar to the following examples:
[TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: 0A000 NativeError: 1 Message: ERROR: cannot truncate a table referenced in a foreign key constraint; Error while executing the query  (ar_odbc_stmt.c:4622)
[TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: HY000 NativeError: 1217 Message: [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.23-log]Cannot delete or update a parent row: a foreign key constraint fails  (ar_odbc_stmt.c:4615)
Ongoing replication uses the Transactional Apply mode that applies the transactions in the same commit order as the source. When the task is in the ongoing replication phase, you can activate foreign key constraints on the target. If you use Batch Apply mode for ongoing replication, then you must deactivate the foreign keys, even during the change data capture (CDC) phase.
To resolve this error, complete either of the following steps:
If the target is a PostgreSQL-compatible database, then you see foreign key violation errors during the CDC phase. To resolve this error, set the session_replication_role parameter to replica. To do this, add the extra connection attribute afterConnectScript=SET session_replication_role='replica' to the endpoint. Or, use the AWS Command Line Interface to add endpoint settings to the target endpoint.
For other databases engines, manually deactivate or drop foreign key constraints.
When you use Drop tables on target mode, AWS DMS creates only objects that are necessary for a full load to succeed on the target. AWS DMS also refers to this as the DROP_AND_CREATE task setting. However, if you use Drop tables on target mode, then you must manually create other objects outside of AWS DMS. This includes objects such as secondary indexes, data defaults, and triggers.