Problems Updating Database Tables (MySQL)

I run into a little problem a few days ago when I was trying to update a database table’s structure. The table had been created with the wrong settings, and I had to go and add an AUTO_INCREMENT to the primary key. Before you get out of here thinking I’m such a stupid person, you should know that the table was created, not by me, but by a person who is bravely learning about this stuff.

The table is using InnoDB engine, which enforces constraints between tables, and this was causing me not to be able to change the table. Everytime I tried, I jut got back an error similar to this:

ERROR 1025 (HY000): Error on rename of './e3/#sql-17f3_f894' to './e3/cls_ev_items' (errno: 150)

To solve this problem, one just needs to remove the contraint, change the database, and add the constraint again.

To remove the contraint you would do something like this:

ALTER TABLE table_name DROP FOREIGN KEY relationship_name;

Then you can alter your table, in my case I did something like this:


Then add the constraint again:

ALTER TABLE table_name ADD CONSTRAINT `relationship_name` FOREIGN KEY(`field_in_table`) REFERENCES `other_table` (`foreing_key`);

And that is basically all you have to do. Im my case, all the tables were missing the AUTO_INCREMENT, so I just DROPed them all and creted them anew with the right settings, but sometimes that is not possible.

Some links that were useful, or maybe not, are recorder here for future reference: