MySQL alter table - changing a TINYINT to a SMALLINT

Question

I have the following database with two tables (excuse my ugly format):

TABLE_A : Id TINYINT, PRIMARY KEY (Id)
TABLE_B : Id TINYINT, FOREIGN KEY (Id) REFERENCES TABLE_A (Id)

I would like to modify Id so that it's a SMALLINT rather than a TINYINT. Here are a couple failed attempts:

mysql> ALTER TABLE TABLE_A MODIFY Id SMALLINT
ERROR 1025 (HY000): Error on rename of '.\testdb\#sql-bcc_16' to '.\testdb\table_a' (errno: 150)

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

Does anyone know what's going on here?

Answer 1

Use below queries

mysql> ALTER TABLE Price MODIFY Id SMALLINT

mysql> ALTER TABLE Sale MODIFY Id SMALLINT

mysql> ALTER TABLE Phone MODIFY Id SMALLINT

I also answered it at MySQL alter table generating "error on rename" long time back... but you are not replying there...

Here in your case it will be

mysql> ALTER TABLE TABLE_A MODIFY Id SMALLINT

mysql> ALTER TABLE TABLE_B MODIFY Id SMALLINT

Update

check Help with: ERROR 1025 (HY000): Error on rename of .... (errno: 150).. Might help you..