MySQL alter table生成“重命名错误". [英] MySQL alter table generating "error on rename"

问题描述

这是我的错误的打印:

mysql> ALTER TABLE Price DROP FOREIGN KEY Id
ERROR 1025 (HY000): Error on rename of '.\testdb\Price' to '.\t
estdb\#sql2-bcc-16' (errno: 152)

我已阅读此页面，这表明问题可能是由于早期的alter table错误导致表剩余而引起的，并建议您在数据目录中窥探一下并删除任何名称如"B-xxx.frm"的表.

我的错误与此类似，但是我的数据目录中没有#sql2-bcc-16".无论如何，不​​会有先前失败的alter table的任何工件，因为这只是一个很小的测试数据库，而我之前实际上并没有更改任何表.

下面有更多信息.

我真正想做的是将另一个表中的ID(主键)更改为SMALLINT(而不是TINYINT).但是，此电话表的ID是外键，引用了另一个表中的ID.因此，我认为在进行主表的类型更改之前，需要删除该表的外键.我希望这很清楚.

表格.

销售-ID TINYINT不为NULL，主键(Id)

电话-ID TINYINT不为空，外键(Id)参考销售(Id)

我希望数据库中的所有ID为SMALLINT，而不是TINYINT.那就是我目前的情况.

解决方案

尝试使用mysql> ALTER TABLE Price DROP Id

希望它能起作用...祝你好运

我尝试过与您查询mysql> ALTER TABLE Price DROP FOREIGN KEY Id

它执行了，但是没用！！

更新:

在下面的查询中使用...

mysql> ALTER TABLE Price MODIFY Id SMALLINT

mysql> ALTER TABLE Sale MODIFY Id SMALLINT

mysql> ALTER TABLE Phone MODIFY Id SMALLINT

Here's a print of my error:

mysql> ALTER TABLE Price DROP FOREIGN KEY Id
ERROR 1025 (HY000): Error on rename of '.\testdb\Price' to '.\t
estdb\#sql2-bcc-16' (errno: 152)

I've read this page which suggests that the problem may be due to a left-over table from some earlier alter table error, and recommends you snoop around in your data directory and delete any table with a name such as 'B-xxx.frm'.

My error is similar to this, but there is no '#sql2-bcc-16' in my data directory. And there wouldn't be any artifacts of a previous failed alter table, anyway, because this is just a small test database and I haven't actually altered any tables previously.

EDIT: More info below.

What I really want to do is change an Id (primary key) in another table to be a SMALLINT (instead of a TINYINT). However, THIS Phone table's Id is a foreign key, referencing Id in the other table. So, I believe I need to drop the foreign key of this table before proceeding with the type-change in the primary table. I hope this is clear.

EDIT 2: Tables.

Sale - has Id TINYINT NOT NULL, PRIMARY KEY (Id)

Phone - has Id TINYINT NOT NULL, FOREIGN KEY (Id) REFERENCES Sale (Id)

I would like all Ids in my database to be SMALLINT and not TINYINT. That's my current situation.

解决方案

try with mysql> ALTER TABLE Price DROP Id

Hope it works... Good Luck

I tried with you query mysql> ALTER TABLE Price DROP FOREIGN KEY Id

It executes, but didn't worked!!!

Update:

Use below queries...

mysql> ALTER TABLE Price MODIFY Id SMALLINT

mysql> ALTER TABLE Sale MODIFY Id SMALLINT

mysql> ALTER TABLE Phone MODIFY Id SMALLINT

这篇关于MySQL alter table生成“重命名错误".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！