mysql 错误号1553 MySQL Cannot drop index needed in a foreign key constraint,外键也会创建索引

╰半夏微凉° 2022-05-15 01:12 1100阅读 0赞

## 表 ##

被参照表:

| teacher | CREATE TABLE `teacher` ( `id` varchar(20) NOT NULL, `name` varchar(255) DEFAULT NULL, `salary` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

参照表:

| teacher_student | CREATE TABLE `teacher_student` ( `teacher_id` varchar(20) NOT NULL, `student_id` varchar(20) NOT NULL, PRIMARY KEY (`teacher_id`,`student_id`), KEY `student_id_FK` (`student_id`), CONSTRAINT `student_id_FK` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `teacher_id_PK` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`teacher_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

## 错误再现 ##

当外键约束完成的时候，我在用自然连接的时候发现，id写错了，于是我想改一下。这一改就改出了一个知识 - -  
![这里写图片描述][70]

## 分析原因 ##

字面意思是，mysql不能drop掉索引 ‘primary’，在外键约束上被需要。就是说，主键上是有名为`primary`的索引的，并且关联了外键，这个时候是无法对这个被参照的对象进行修改(删除)的。

## 解决问题 ##

删除外键。

mysql> alter table teacher_student drop foreign key student_id_FK;
    Query OK, 0 rows affected (0.06 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> alter table teacher_student drop foreign key teacher_id_PK;
    Query OK, 0 rows affected (0.07 sec)
    Records: 0  Duplicates: 0  Warnings: 0

将两个外键先删掉之后，便可以修改被参照表的列名，那么为什么会存在这样的问题呢？现在想想其实很明显，建立表的时候 我们协商了约束条件，写好了references 表名(列名)，这个时候如果改掉了被参照的列名，那么这个约束自然出现了问题。

## 更进一步-外键也会有索引 ##

看到了索引这个东西，我忽然想起了，之前的一个印象，主键会创建索引，但是我在navicat上 点击索引的时候，奇怪的事情发生了，上面竟然没有写- -主键索引，于是我就用命令行去查看了一下。如下:

由此验证了，确实会创建主键索引，这么一看就看见了一个我之前不知道的东西，这边多了一个索引。这个索引来自何处？于是我便又查看了create table：

| teacher_student | CREATE TABLE `teacher_student` ( `teacher_id` varchar(20) NOT NULL, `student_id` varchar(20) NOT NULL, PRIMARY KEY (`teacher_id`,`student_id`), /*这里多了一个ｋｅｙ*/ KEY `student_id_FK` (`student_id`), CONSTRAINT `student_id_FK` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `teacher_id_PK` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`teacher_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

意外发现，这边多了一个key，我记得很清楚，当时建表的时候我并没有指定这个key，查了点资料发现可能是由于外键的约束而产生了一个普通的key，普通的key可以作为是一种普通的index作用，于是便有了下面的试验。test作为参照列，只指定primary key。show create table之后:

| test  | CREATE TABLE `test` ( `name` varchar(255) NOT NULL, `test` varchar(20) DEFAULT NULL, PRIMARY KEY (`name`), KEY `test` (`test`), CONSTRAINT `test` FOREIGN KEY (`test`) REFERENCES `teacher` (`teacher_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

果不其然，在创建外键的时候也会给它一个索引，会建立一个key，所以至少得到一个结论，除了主键，外键也会建立索引的～

## 总结 ##

这次主要是记录下约束了外键之后，再修改表的方法，顺便还知道了外键也会创建索引，还学会了几条命令。

附上各种修改操作命令:  
![这里写图片描述][70 1]

[70]: /images/20220515/0ff8e2701bb646fe837d3a63124dec20.png
[70 1]: /images/20220515/79b6a5bf3eed4d15b34e87bfa2739a70.png