tencent cloud

Feedback

Foreign Key Dependency Check

Last updated: 2024-07-08 19:41:52

    Check Details

    Data migration among MySQL, MariaDB, Percona, and TDSQL-C for MySQL: Foreign key dependency check can be set to CASCADE, SET NULL, RESTRICT, NO ACTION, or SET DEFAULT. You need to enable this check item first.
    Data sync among MySQL, MariaDB, Percona, and TDSQL-C for MySQL: Foreign key dependency check can be set only to NO ACTION or RESTRICT; otherwise, an error will occur, but you can choose whether to fix the error or ignore it and proceed with the task.
    TDSQL for MySQL data migration and sync: Foreign key dependency check can be set only to NO ACTION or RESTRICT; otherwise, an error will occur, but you can choose whether to fix the error or ignore it and proceed with the task.
    TDSQL for TDStore data migration: Foreign key-dependent data is not supported. If the source database has such data, the task verification will report an error.
    During partial table migration, tables with foreign key dependency must be migrated.

    Foreign Key Dependency Parameters

    When you set a foreign key in MySQL, there are four values that can be selected for the ON DELETE and ON UPDATE columns:
    CASCADE: When a record is deleted or updated in the parent table, its associated records will also be deleted or updated in the child table.
    SET NULL: When a record is deleted or updated in the parent table, the column of the foreign key field of its associated records will be set to null in the child table (child table foreign keys cannot be set to not null).
    RESTRICT: When a record is deleted or updated in the parent table, if it is associated with records in the child table, the deletion request in the parent table will be denied.
    NO ACTION: Similar to RESTRICT, the foreign key will be checked first.
    SET DEFAULT: When a record is deleted or updated in the parent table, the foreign key column in the child table will be set to the default value, but the InnoDB engine cannot recognize it.

    Ignoring Errors or Enabling Foreign Key Dependency Migration

    For data migration among MySQL, MariaDB, Percona, and TDSQL-C for MySQL, foreign key dependency check can be set to CASCADE, SET NULL, RESTRICT, NO ACTION, or SET DEFAULT.
    DTS supports RESTRICT and NO ACTION by default. If the data migrated from the source database has other types of foreign key dependency configuration, the check system will report an error, and you need to enable foreign key dependency check as a check item as follows:
    1.1 On the Verify task page during data migration, the source database has foreign key dependency rules CASCADE, SET NULL, and SET DEFAULT, and an error is reported for the check item. You can view the details of the check item.
    
    2. Choose to ignore the error or migrate the foreign key dependency. After confirming your selection, indicate your consent and click OK.
    Option
    Description
    Ignore foreign key dependency check error
    After this option is selected, the system will ignore existing check errors and proceed with the migration task.
    If the source database has foreign key rules CASCADE, SET NULL, or SET DEFAULT, the data can still be migrated, but the data results between the source and target databases may be inconsistent if data in the parent table is deleted or updated after errors are ignored.
    Migrate foreign key dependency
    After this option is selected, CASCADE, SET NULL, and SET DEFAULT foreign key dependencies can be migrated, but DTS will check the foreign key dependencies only when a task is initiated to guarantee the consistency of the existing data migrated to the target database.
    1. During migration, do not modify the foreign key dependency check rule in the source database; otherwise, the data in the source and target databases will become inconsistent. If you change the foreign key dependency check rule in the source database from CASCADE to NO ACTION during migration, updates and deletions performed on the parent table in the target database will still affect child tables.
    2. If the foreign key dependency check rule is CASCADE or SET NULL, after you select Migrate foreign key dependency, tables in the source database will be locked for about 5–10 seconds during full migration.
    3. To guarantee the data consistency, during incremental sync, the original row-level sync of data in foreign key-related tables will be downgraded to table-level, and the performance will decrease.
    
    3. Run the verification task again.

    Fixing an Error

    Data sync among MySQL, MariaDB, Percona, and TDSQL-C for MySQL: Foreign key dependency check can be set only to NO ACTION or RESTRICT; otherwise, an error will occur, but you can choose whether to fix the error or ignore it and proceed with the task. You can fix the error as follows:
    TDSQL for MySQL data migration and sync: Foreign key dependency check can be set only to NO ACTION or RESTRICT; otherwise, an error will occur, but you can choose whether to fix the error or ignore it and proceed with the task. You can fix the error as follows:
    TDSQL for MySQL (TDStore Edition): Foreign key-dependent data is not supported. You can proceed with the task only after deleting the corresponding foreign key parameter.

    Modifying a foreign key rule

    Windows

    1. Log in to DMC in the source database as instructed in DMC Management.
    2. Select the table to be modified in the target tree on the left and click the Foreign Key tab on the opened table editing page to modify the foreign key parameter.
    
    
    3. After completing the modification, click Save.
    4. Run the verification task again.

    Linux

    1. Log in to the source database as instructed in Connecting to MySQL Instance.
    2. Delete the original foreign key settings.
    alter table `table name 1` drop foreign key `foreign key name 1`;
    3. Add the foreign key settings again.
    alter table `table name 1` add constraint `foreign key name 2` foreign key `table name 1`(`column name 1`) references `table name 2`(`column name 1`)
    on update no action on delete no action;
    4. Run the verification task again.

    Completing migration objects

    When modifying the migration task configuration, include objects with associations in Migration Object.
    1. Log in to the DTS console, select the target migration task, and click More > Modify in the Operation column.
    2. Select the objects with associations in Migration Object.
    3. Run the verification task again.
    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support