A company is migrating a PostgreSQL database to Amazon Aurora PostgreSQL. To minimize downtime, they plan to use the AWS DMS with change data capture (CDC). During the full load phase, DMS reports an error: 'Failed to add foreign key constraint'. What is the most likely cause?
DMS applies constraints after loading data, and if data violates them, the task fails.
Why this answer
The error 'Failed to add foreign key constraint' during the full load phase of AWS DMS indicates that the source data contains referential integrity violations. DMS attempts to apply foreign key constraints on the target Aurora PostgreSQL after loading data, but if parent-child relationships are broken (e.g., orphan rows), the constraint creation fails. This is a common issue when migrating databases with existing data integrity problems, as DMS does not automatically validate or fix source data.
Exam trap
The trap here is that candidates assume the error is due to a DMS limitation or configuration issue, rather than recognizing it as a data integrity problem in the source database that must be resolved before migration.
How to eliminate wrong answers
Option A is wrong because CDC (change data capture) is fully compatible with foreign key constraints; DMS handles constraints by applying them after the full load and during ongoing replication. Option C is wrong because the target table lacking a primary key would cause a different error (e.g., 'No primary key defined' or issues with CDC), not a foreign key constraint failure. Option D is wrong because data type mismatches between source and target typically cause row-level conversion errors or truncation warnings, not a failure to add a foreign key constraint.