mysql needed in a foreign key constraint_mysql 错误号1553 MySQL Cannot drop index needed in a foreign k...

表

被参照表:

| 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，所以至少得到一个结论，除了主键，外键也会建立索引的～

总结

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

附上各种修改操作命令: