Friday, October 17, 2008

MySQL issue: delete column :)

I faced very simple task: remove unused column in database table. Issue was in fact that mentioned column is a foreign key to another table.
After trying to run:
alter table table_name drop column_name;
strange message appeared:
Error on rename of '.\database\#sql-6ec-13' to '.\database\' (errno: 150)
This is because adding foreign keys also adds an index key to the table (for optimization needs).Therefore to delete mentioned column You need:
  1. Run show create table table_name and see what keys and foreign keys are created on mentioned column;
  2. Delete all keys using alter table table_name drop key key_name;
  3. Delete all foreign keys using alter table table_name drop foreign key foreign_key_name;
  4. Drop needed column alter table table_name drop column_name;
That's all.

No comments: