In RDS Replication primary key, foreign key, and constraints are not replicating.

Question

Hello experts, 
I have been going through a problem. At the time of replication, I found only tables, column names, and data had been transferred.

In the source db, there are primary key, foreign key, and constraints, which are missing in the target after replication.

Like the following are missing.

UNIQUE KEY `clients_uuid_unique` (`uuid`),
  UNIQUE KEY `clients_name_unique` (`name`),
  KEY `clients_client_group_id_foreign` (`client_group_id`),
  KEY `clients_primary_contact_id_foreign` (`primary_contact_id`),
  KEY `clients_state_id_foreign` (`state_id`),
  CONSTRAINT `clients_client_group_id_foreign` FOREIGN KEY (`client_group_id`) REFERENCES `client_groups` (`id`),
  CONSTRAINT `clients_primary_contact_id_foreign` FOREIGN KEY (`primary_contact_id`) REFERENCES `users` (`id`),
  CONSTRAINT `clients_state_id_foreign` FOREIGN KEY (`state_id`) REFERENCES `states` (`id`)

Here is the rules: 
{
    "rules": [
        {
            "rule-type": "selection",
            "rule-id": "798475684",
            "rule-name": "798475684",
            "object-locator": {
                "schema-name": "global-db",
                "table-name": "%"
            },
            "rule-action": "include",
            "filters": [],
            "parallel-load": null,
            "isAutoSegmentationChecked": false
        }
    ]
}

Answer

Please note that DMS only migrates data carrying objects table data with atleast pk or unique key.
If you are migrating from same engine, you can script out the table definition and apply the same at target before migrating the data with DMS. 
Also not you need to make sure DMS task setting target table preparation mode is set to truncate_only and not drop and recreate tables.

If you are using different database engines you can use Schema Conversion Tool to convert and apply table definition https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html

Please note sct would also help with secondary indexes, foreign keys, stored procedures functions triggers etc.

In RDS Replication primary key, foreign key, and constraints are not replicating.

相关内容