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> show index 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> show keys from CORE_FUNCTION; +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec)

顯然這是MYSQL的一個BUG:

删除外鍵的時候,這個同名索引如果沒被删,則MYSQL認為外鍵