The behavior of TiDB foreign keys is inconsistent with MySQL

[yao-2110]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table b (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `f` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

create table a (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `b_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_b_id` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

insert b(id,f) values(1,1);
insert a(id,b_id) values(1,1);
update b set id=1,f=2 where id=1;

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

mysql> use test;
Database changed
mysql> create table b (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `f` int(11) NOT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> create table a (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `b_id` int(11) NOT NULL,
    ->  PRIMARY KEY (`id`),
    ->   CONSTRAINT `fk_b_id` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`) ON DELETE CASCADE
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> insert b(id,f) values(1,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert a(id,b_id) values(1,1);
Query OK, 1 row affected (0.01 sec)

mysql> update b set id=1,f=2 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.36-log |
+------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

mysql> use test;
Database changed
mysql> create table b (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `f` int(11) NOT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.52 sec)

mysql> create table a (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `b_id` int(11) NOT NULL,
    ->  PRIMARY KEY (`id`),
    ->  CONSTRAINT `fk_b_id` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`) ON DELETE CASCADE
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.52 sec)

mysql> insert b(id,f) values(1,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert a(id,b_id) values(1,1);
Query OK, 1 row affected (0.02 sec)

mysql> update b set id=1,f=2 where id=1;
**ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`a`, CONSTRAINT `fk_b_id` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`) ON DELETE CASCADE)**

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.7.25-TiDB-v7.1.0 |
+--------------------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.1.0
Edition: Community
Git Commit Hash: 635a4362235e8a3c0043542e629532e3c7bb2756
Git Branch: heads/refs/tags/v7.1.0
UTC Build Time: 2023-05-30 10:58:57
GoVersion: go1.20.3
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[borissavelev]

Please include this in 7.1.1 🙏

[crazycs520]

Please include this in 7.1.1 🙏

#44928 is merged, v7.1.1 will contain this fix.

[dveeden]

Confirming this is fixed:

sql> create table b (
   ->   `id` int(11) NOT NULL AUTO_INCREMENT,
   ->   `f` int(11) NOT NULL,
   ->   PRIMARY KEY (`id`)
   -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.1686 sec)

sql> 

sql> create table a (
   ->   `id` int(11) NOT NULL AUTO_INCREMENT,
   ->   `b_id` int(11) NOT NULL,
   ->   PRIMARY KEY (`id`),
   ->   CONSTRAINT `fk_b_id` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`) ON DELETE CASCADE
   -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.2429 sec)

sql> 

sql> insert b(id,f) values(1,1);
Query OK, 1 row affected (0.0311 sec)

sql> insert a(id,b_id) values(1,1);
Query OK, 1 row affected (0.0151 sec)

sql> update b set id=1,f=2 where id=1;
Query OK, 1 row affected (0.0185 sec)

Rows matched: 1  Changed: 1  Warnings: 0

sql> SELECT TIDB_VERSION()\G
*************************** 1. row ***************************
TIDB_VERSION(): Release Version: v7.1.1
Edition: Community
Git Commit Hash: cf441574864be63938524e7dfcf7cc659edc3dd8
Git Branch: heads/refs/tags/v7.1.1
UTC Build Time: 2023-07-19 10:20:53
GoVersion: go1.20.6
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv
1 row in set (0.0010 sec)