Avoid issues caused by foreign key constraints - Data Transmission Service

This topic describes how to avoid data inconsistency caused by foreign key constraints of the source database, such as FOREIGN KEY REFERENCES ON DELETE CASCADE and FOREIGN KEY REFERENCES ON UPDATE CASCADE.

Background information

If you delete or update the data in parent tables in a MySQL database that has foreign key constraints, such as FOREIGN KEY REFERENCES ON DELETE CASCADE and FOREIGN KEY REFERENCES ON UPDATE CASCADE, the related data in child tables are automatically deleted or updated. Operations that automatically delete or update data in these child tables are not recorded in binary logs.

Impacts

When you use Data Transmission Service (DTS) to migrate, synchronize, or track the incremental data in a source database with foreign key constraints, DTS cannot retrieve specific delete or update operations from binary logs. This may cause data inconsistency or data loss. For example, the data volume of child tables may be larger than that of parent tables in the destination database.

Scenarios

The Database Type of the source database is MySQL, PolarDB for MySQL, MariaDB, PolarDB-X 1.0, PolarDB-X 2.0, or ApsaraDB OceanBase for MySQL.

Solutions

This section describes how to manually manage delete and update operations or use triggers to avoid data inconsistency caused by foreign key constraints.

Important

We recommend that you execute the following SQL statements in Data Management (DMS). If you execute the SQL statements in other MySQL clients, the results may be different.

Sample code

Create a parent table

-- Create a parent table.
CREATE TABLE parent (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Insert data.
INSERT INTO parent (id, name) VALUES (1, 'Parent 1'), (2, 'Parent 2');

Create a child table

-- Create a child table with the foreign key constraint FOREIGN KEY REFERENCES ON DELETE CASCADE.
CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(50),
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Insert data.
INSERT INTO child (id, parent_id, name) VALUES (1, 1, 'Child 1'), (2, 1, 'Child 2'), (3, 2, 'Child 3');

Procedure

Manually manage delete and update operations

You can manually delete or update related data in child tables before you delete or update data in parent tables. This way, all delete and update operations are recorded in binary logs. Sample code:

-- Manually delete the related record in the child table when a record in the parent table is deleted.
DELETE FROM child WHERE parent_id = 1;
DELETE FROM parent WHERE id = 1;

-- Manually update the related record in the child table when a record in the parent table is updated.
UPDATE parent SET id = 3 WHERE id = 2;
UPDATE child SET parent_id = 3 WHERE parent_id = 2;

Use triggers

You can use MySQL triggers to automatically delete or update related data in child tables when data in parent tables is deleted or updated. This way, all delete and update operations are recorded in binary logs. Sample code:

-- Create a trigger.
DELIMITER //

CREATE TRIGGER delete_parent_trigger
BEFORE DELETE ON parent
FOR EACH ROW
BEGIN
    DELETE FROM child WHERE parent_id = OLD.id;
END //

CREATE TRIGGER update_parent_trigger
AFTER UPDATE ON parent
FOR EACH ROW
BEGIN
    UPDATE child SET parent_id = NEW.id WHERE parent_id = OLD.id;
END //

DELIMITER ;

-- Insert data.
INSERT INTO parent (id, name) VALUES (3, 'Parent 3');
INSERT INTO child (id, parent_id, name) VALUES (4, 3, 'Child 4');

-- Delete data from the parent table.
DELETE FROM parent WHERE id = 3;

-- Insert data and update.
INSERT INTO parent (id, name) VALUES (4, 'Parent 4');
INSERT INTO child (id, parent_id, name) VALUES (5, 4, 'Child 5');
UPDATE parent SET id = 5 WHERE id = 4;

Summary

You can manually manage delete and update operations or use triggers to ensure that the delete and update operations triggered by the foreign key constraints of the source MySQL database, such as FOREIGN KEY REFERENCES ON DELETE CASCADE and FOREIGN KEY REFERENCES ON UPDATE CASCADE, are accurately recorded in binary logs while a DTS instance is running.