mysql修改外键字段报异常_mysql外键使用错误字段名称,不能删除或修改字段名

mysql> alter table gradeinfo change s_num num int(10);

ERROR 1025 (HY000): Error on rename of '.\example\#sql-788_4' to '.\example\gradeinfo' (errno: 150)

网上查出的原因:

真实的原因及解决办法:

show index 发现有和外键同名的索引存在

mysql>showindex from CORE_FUNCTION;

+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| CORE_FUNCTION | 0 | PRIMARY | 1 | FUNCTION_ID | A | 0 | NULL | NULL | | BTREE | |

| CORE_FUNCTION | 1 |FKDB8410785D1C928B | 1 | PARENT | A | 0 | NULL | NULL | YES | BTREE | |

+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set (0.01 sec)

删除掉索引

mysql> alter table CORE_FUNCTION drop index FKDB8410785D1C928B;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

再show keys看,外键不见了

mysql> showkeys from CORE_FUNCTION;

+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| CORE_FUNCTION | 0 | PRIMARY | 1 | FUNCTION_ID | A | 0 | NULL | NULL | | BTREE | |

+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

1 row in set (0.00 sec)

显然这是MYSQL的一个BUG:

1.MYSQL在建外键后,会自动建一个同名的索引

2.而

删除外键的时候,这个同名索引如果没被删,则MYSQL认为外键