pingcap / pingcap/tidb

`INSERT IGNORE ON DUPLICATE KEY UPDATE` bypasses foreign key constraint and incorrectly updates parent row

Open
#66,808 2 comments 0 reactions 0 assignees View on GitHub
affects-8.5 contribution may-affects-7.1 may-affects-7.5 may-affects-8.1 severity/critical sig/sql-infra type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)
```SQL
mysql> create table parent (id int primary key, ref int, key(ref));
Query OK, 0 rows affected (0.02 sec)

mysql> create table child (id int primary key, ref int, foreign key (ref) references parent(ref));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into parent values (1, 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into child values (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into parent values (1, 3) on duplicate key update ref = 3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`ref`) REFERENCES `parent` (`ref`))
mysql> select * from parent;
+----+------+
| id | ref |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)

mysql> insert ignore into parent values (1, 3) on duplicate key update ref = 3;
Query OK, 2 rows affected (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> select * from parent;
+----+------+
| id | ref |
+----+------+
| 1 | 3 |
+----+------+
1 row in set (0.00 sec)
```

### 2. What did you expect to see? (Required)
The statement with IGNORE should downgrade the error to a warning and skip the update to maintain the foreign key constraint. The data in the parent table should remain unchanged.
```SQL
mysql> insert ignore into parent values (1, 3) on duplicate key update ref = 3;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1451 | Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`ref`) REFERENCES `parent` (`ref`)) |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from parent;
+----+------+
| id | ref |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.45 |
+-----------+
1 row in set (0.01 sec)
```

### 3. What did you see instead (Required)
```SQL
mysql> insert ignore into parent values (1, 3) on duplicate key update ref = 3;
Query OK, 2 rows affected (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> select * from parent;
+----+------+
| id | ref |
+----+------+
| 1 | 3 |
+----+------+
1 row in set (0.00 sec)
```

### 4. What is your TiDB version? (Required)
```SQL
mysql> select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v9.0.0-beta.2.pre-1319-g7d77215b44
Edition: Community
Git Commit Hash: 7d77215b44b7fefce8495ee0dd42c8b9f9253213
Git Branch: master
UTC Build Time: 2026-03-09 07:47:46
GoVersion: go1.25.6
Race Enabled: false
Check Table Before Drop: false
Store: unistore
Kernel Type: Classic |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

Contributor guide

Open the contributing guide

Research direction

Start by running the provided parent/child table reproduction against TiDB and compare INSERT IGNORE ... ON DUPLICATE KEY UPDATE with the non-IGNORE form. Trace the foreign-key and duplicate-key update handling from that SQL entry point, then add regression coverage showing that the update is skipped and warning 1451 is reported while the parent row remains unchanged.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.