Friday, 17 May 2013

I do not want them Sam I am

This morning I updated the CTH website and prepared my pog website update.

In the afternoon I finished preparing the pog website update, and fixed a problem with my photo website.

When trying to fix my photo website I would get an error errno: 150 in mysql when trying to drop then add a table. Eventually I tracked the problem down. I was changing the type of a column from INT(10) to TINYINT(2), but this column was referenced from another table with a foreign key constraint. The column type in other table was INT(10).

This thread on stackoverflow was very helpful in helping me discover the problem: MySQL Creating tables with Foreign Keys giving errno: 150. The column with the foreign key constraint and the column in the other table must have the same type (also including being unsigned or signed). Having FOREIGN_KEY_CHECKS=0 does not affect this.

The problem with this was, how do you change it? You can't alter either of the columns because the other column will be of a different type and cause the error. It's a bit of a catch 22 - You can't alter the referenced column type because it will mismatch with the column that has the foreign key constraint on it. But you can't alter the column that has the foreign key constraint on it because it will mismatch with the referenced column type.

The answer is to drop the foreign key constraint, modify your columns, then add the constraint back again. Info on that I found at stack overflow again: How to alter Foreign Keys in phpMyAdmin.

In the evening I watched For Your Eyes Only with Mauser and Diddle Bo. Very maniacal.

No comments: