Posts

Showing posts from July, 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 b