Create index doesn't rename the old index that has same columns as the new one

[Mini256]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE A (
  id  int primary key,
  foo int not null
);

CREATE TABLE B (
  id int primary key
);

ALTER TABLE A ADD CONSTRAINT fk_1 FOREIGN KEY (foo) REFERENCES B(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE A ADD CONSTRAINT fk_2 FOREIGN KEY (foo) REFERENCES B(id) ON DELETE RESTRICT ON UPDATE RESTRICT;

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

The first index name fk_1 has been renamed to fk_2.

mysql> show create table A\G
*************************** 1. row ***************************
       Table: A
Create Table: CREATE TABLE `A` (
  `id` int(11) NOT NULL,
  `foo` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_2` (`foo`),
  CONSTRAINT `fk_1` FOREIGN KEY (`foo`) REFERENCES `B` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_2` FOREIGN KEY (`foo`) REFERENCES `B` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

3. What did you see instead (Required)

The first index name fk_1 remains unchanged.

tidb> show create table A\G
*************************** 1. row ***************************
       Table: A
Create Table: CREATE TABLE `A` (
  `id` int(11) NOT NULL,
  `foo` int(11) NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `fk_1` (`foo`),
  CONSTRAINT `fk_1` FOREIGN KEY (`foo`) REFERENCES `multiple_foreign_key_constraints_are_taken_always_in_the_same_or`.`B` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_2` FOREIGN KEY (`foo`) REFERENCES `multiple_foreign_key_constraints_are_taken_always_in_the_same_or`.`B` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)

4. What is your TiDB version? (Required)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v7.0.0
Edition: Community
Git Commit Hash: 7376954cd868dbc44fc3015c9ef89c53749339a7
Git Branch: heads/refs/tags/v7.0.0
UTC Build Time: 2023-03-29 13:22:40
GoVersion: go1.20.2
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

[Mini256]

Another case:

DDL:

CREATE TABLE `User` (
  `id` INTEGER AUTO_INCREMENT NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `Post` (
  `id` INTEGER AUTO_INCREMENT NOT NULL,
  `user_id` INTEGER NOT NULL,
  CONSTRAINT CustomFKName FOREIGN KEY (`user_id`) REFERENCES `User`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  PRIMARY KEY (`id`)
);

CREATE INDEX `Post_user_id_idx` ON `Post` (`user_id`);

TiDB:

Both indexes are retained (CustomFKName and Post_user_id_idx), this may lead to redundant indexes. 😢

tidb> show create table Post\G
*************************** 1. row ***************************
       Table: Post
Create Table: CREATE TABLE `Post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `CustomFKName` (`user_id`),
  KEY `Post_user_id_idx` (`user_id`),
  CONSTRAINT `CustomFKName` FOREIGN KEY (`user_id`) REFERENCES `t3`.`User` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)

MySQL:

The old index CustomFKName is renamed to Post_user_id_idx.

mysql> show create table Post\G
*************************** 1. row ***************************
       Table: Post
Create Table: CREATE TABLE `Post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Post_user_id_idx` (`user_id`),
  CONSTRAINT `CustomFKName` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.05 sec)

[crazycs520]

Nice catch. This is a compatibility issue with MySQL, But this doesn't affect correctness. If necessary, I might fix it in the future.

[Mini256]

/type compatibility

[xuyukeviki]

/assign

[crazycs520]

Since this compatibility issue doesn't affect correctness, and the behavior of MySQL is a bit strange, I don't plan to fix this issue currently, if you have a rush demand, please leave a comment describing your needs and problem scenarios.