pingcap / pingcap/tidb

foreign key consistency broken

Open
#62,850 10 comments 0 reactions 1 assignee Claimed by @YangKeao View on GitHub
component/unistore severity/minor 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)

```
CREATE TABLE `parent` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `child` (
`v` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
KEY `fk_1` (`parent_id`),
CONSTRAINT `fk_1` FOREIGN KEY (`parent_id`) REFERENCES `test`.`parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `parent` values (1), (2), (3);
```

Now use two mysql client, and mysql1:

```
begin;
insert into child values (3, 3);
```

mysql2:

```
update parent set id = 5 where id = 3;
```

mysql1:

```
commit;
```

### 2. What did you expect to see? (Required)

```
mysql> select * from parent;
+----+
| id |
+----+
| 1 |
| 2 |
| 5 |
+----+
3 rows in set (0.01 sec)

mysql> select * from child;
+------+-----------+
| v | parent_id |
+------+-----------+
| 3 | 5 |
+------+-----------+
1 row in set (0.00 sec)
```

### 3. What did you see instead (Required)

```
mysql> select * from parent;
+----+
| id |
+----+
| 1 |
| 2 |
| 5 |
+----+
3 rows in set (0.01 sec)

mysql> select * from child;
+------+-----------+
| v | parent_id |
+------+-----------+
| 3 | 3 |
+------+-----------+
1 row in set (0.00 sec)
```

As you can see, foreign key constraints is broken.
All parent_id values in the child table should exist in the parent table's id!

### 4. What is your TiDB version? (Required)

master branch

```
commit 0906244b995c7b92f17ccdd834e359e8349d6f61 (HEAD -> master, origin/master, origin/HEAD)
Author: Weizhen Wang
Date: Tue Jul 29 14:58:22 2025 +0800

planner: no need to check predicate type in the updateInPredicate (#62674)

close pingcap/tidb#62675
```

This bug should affect all versions.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.