MySQL: error 150 on rename column

jplindstrom on 2008-01-21T12:03:48

Dear Google Search Result Page!

So this fails on an InnoDB table in MySQL 5.0:

ALTER TABLE `xxx_table`
    CHANGE COLUMN `importid` `import_id` int(11) NOT NULL;

with this luvely error message:

#   Failed test 'Migrating up to 29 '
#   at t/unit/db/migrations.t line 84.
ERROR : DBD::mysql::db do failed: Error on rename of
'./xxx_tmp_migration_test_1200915479/#sql-4dcf_348f2' to
'./xxx_tmp_migration_test_1200915479/xxx_table' (errno: 150)

Why is this?

At first I thought it was because importid is part of the composite PK (it's a link table), but that's not it.

It turns out that there is a FK constraint on importid to another table. Deleting the FK and reinstating it with the new column name solves the problem.

Thanks for the self explanatory error message MySQL, that was very helpful.