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

表

被参照表:

| 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写错了，于是我想改一下。这一改就改出了一个知识 - -

分析原因

　　字面意思是，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上 点击索引的时候，奇怪的事情发生了，上面竟然没有写- -主键索引，于是我就用命令行去查看了一下。如下:

mysql> show index from teacher_student;
+-----------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| teacher_student |          0 | PRIMARY       |            1 | teacher_id  | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| teacher_student |          0 | PRIMARY       |            2 | student_id  | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| teacher_student |          1 | student_id_FK |            1 | student_id  | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

　　由此验证了，确实会创建主键索引，这么一看就看见了一个我之前不知道的东西，这边多了一个索引。这个索引来自何处？于是我便又查看了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，所以至少得到一个结论，除了主键，外键也会建立索引的～

总结

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

附上各种修改操作命令: