`INSERT IGNORE ON DUPLICATE KEY UPDATE` bypasses foreign key constraint and incorrectly updates parent row
- 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
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