删除外键后，mySQL表中的键值MUL没有变化

Question

student table:
+---------------+----------+------+-----+---------+-------+
| Field         | Type     | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| stu_id        | int      | NO   | PRI | NULL    |       |
| stu_name      | char(30) | YES  | UNI | NULL    |       |
| stu_branch_id | int      | NO   | MUL | NULL    |       |
+---------------+----------+------+-----+---------+-------+
branch table:
+--------------------+----------+------+-----+---------+-------+
| Field              | Type     | Null | Key | Default | Extra |
+--------------------+----------+------+-----+---------+-------+
| branch_id          | int      | NO   | PRI | NULL    |       |
| branch_name        | char(30) | YES  | UNI | NULL    |       |
| branch_building_no | int      | YES  |     | NULL    |       |
+--------------------+----------+------+-----+---------+-------+
mysql> alter table student add constraint fk_student foreign key (stu_branch_id) references branch(branch_id);
Query OK, 5 rows affected (2.69 sec)
Records: 5  Duplicates: 0  Warnings: 0
desc student;
+---------------+----------+------+-----+---------+-------+
| Field         | Type     | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| stu_id        | int      | NO   | PRI | NULL    |       |
| stu_name      | char(30) | NO   |     | NULL    |       |
| stu_branch_id | int      | NO   | MUL | NULL    |       |
+---------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table student 
-> drop foreign key fk_student;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc student;
+---------------+----------+------+-----+---------+-------+
| Field         | Type     | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| stu_id        | int      | NO   | PRI | NULL    |       |
| stu_name      | char(30) | YES  | UNI | NULL    |       |
| stu_branch_id | int      | NO   | MUL | NULL    |       |
+---------------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table student 
-> add constraint fk_student foreign key (stu_id_branch) references branch(branch_id) on delete cascade;
ERROR 1061 (42000): Duplicate key name 'fk_student'

删除外键后MUL键仍然存在。现在，当我尝试添加另一个具有相同名称的外键时，我得到错误。

Answer 1

由隐式索引创建引起的问题。创建外键fk_student索引时，也创建了fk_student。如果需要回滚FK创建，则需要执行以下命令:

alter table student drop foreign key fk_student, drop key fk_student;

看这里的SQL提琴