solutions for foreign key constraints-Data Transmission Service(DTS)-阿里云帮助中心

When a source MySQL database uses ON DELETE CASCADE or ON UPDATE CASCADE foreign key constraints, DTS cannot detect the cascaded changes to child tables through binary logs. This can cause child tables in the destination database to retain rows that were deleted from the source — or end up with more rows than the parent table.

How it works

In a MySQL database with ON DELETE CASCADE or ON UPDATE CASCADE constraints, deleting or updating a row in a parent table automatically deletes or updates the matching rows in child tables. MySQL does not write these cascaded child-table operations to the binary log.

Because Data Transmission Service (DTS) reads changes from binary logs during data migration, synchronization, and incremental data tracking, it cannot capture those cascaded child-table changes. As a result, child tables in the destination database may end up with more rows than in the source, or with stale data that was not deleted.

Affected database types

This issue affects DTS tasks where the source Database Type is one of the following:

MySQL
PolarDB for MySQL
MariaDB
PolarDB-X 1.0
PolarDB-X 2.0
ApsaraDB OceanBase for MySQL

Choose a solution

Two approaches ensure that all delete and update operations are captured in binary logs. Both solutions apply to the source database only.

Solution	How it works
Manually manage operations	Delete or update child table rows before the parent table, so every operation generates its own binary log entry
Use MySQL triggers	Replace the CASCADE constraint with a trigger that explicitly deletes or updates child rows before or after the parent operation

Important

Run all SQL statements in Data Management (DMS). Other MySQL clients may produce different results.

Sample schema

The examples below use the following parent and child tables.

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 ON DELETE CASCADE and ON UPDATE CASCADE constraints.
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');

Solution 1: Manually manage operations

Delete or update the child table rows explicitly before modifying the parent table. Each operation produces its own binary log entry, so DTS can capture the full change sequence.

-- Delete the related child rows before deleting the parent row.
DELETE FROM child WHERE parent_id = 1;
DELETE FROM parent WHERE id = 1;

-- Update the parent row first, then update the matching child rows.
UPDATE parent SET id = 3 WHERE id = 2;
UPDATE child SET parent_id = 3 WHERE parent_id = 2;

Solution 2: Use MySQL triggers

Replace the CASCADE constraint with triggers that explicitly manage child table changes. Use a BEFORE DELETE trigger to delete child rows before the parent row is deleted, and an AFTER UPDATE trigger to update child rows after the parent row is updated.

-- Create a trigger to delete child rows before a parent row is deleted.
DELIMITER //

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

-- Create a trigger to update child rows after a parent row is updated.
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 test data.
INSERT INTO parent (id, name) VALUES (3, 'Parent 3');
INSERT INTO child (id, parent_id, name) VALUES (4, 3, 'Child 4');

-- Delete a parent row -- the trigger deletes the matching child rows.
DELETE FROM parent WHERE id = 3;

-- Insert and update test data.
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;

Both triggers fire before or after the parent table operation, so all changes are written to binary logs in the correct order. DTS can then replicate the full sequence to the destination database without gaps.