Friday, July 6, 2012

Altering a FOREIGN KEY Data Type

Today another curious issue happened regarding MySQL. 

I had a scenario where the rows in a table grew greatly, so that the previous PRIMARY KEY type, a SMALLINT (up to 32767 different ids), could not bear the new amount of data.
So I had to modify the PRIMARYKEY type.

When trying to alter the field, the following error appeared:

ERROR 1025 (HY000): Error on rename of '.\temp\#sql-248_4' to '.\temp\country' (errno: 150)input

Checking this error number in a shell:

$ perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

Basically this error was a consequence of trying to modify a field that was a FOREIGN KEY, with the command

ALTER TABLE mytable MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;

The problem is that in MySQL there is no way of updating a field's type "on cascade". So to update a field that is a FOREIGN KEY one should drop the FOREIGN KEY Relation, change the field type, and then bring the relation back on.
You could also delete the table that uses the FOREIGN KEY, alter the desired field, and then re-create the dropped table.