mysql alter int column to bigint with foreign keys

Even with SET foreign_key_checks = 0, you can't alter the type of the constraint column.From MySQL doc : http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

However, even if foreign_key_checks = 0, InnoDB does not permit the creation of a foreign key constraint where a column references a nonmatching column type.

So, I agree with the comment of Devart. Just drop it and create it again.

I could suggest you to rename such fields in GUI tool - dbForge Studio for MySQL (free trial edition):

Just select the field you want to rename in the Database Explorer, click on Refactoring->Rename command, enter new name in openned window, and press OK, it will rename the field and recreate all foreign keys automatically.

Even though you changed the column size of id and thing_id, the raw data in the rows is still 4 byte integers instead of 8 byte integers. But you can convert the data.

Try this solution instead. I had to do something similar recently on a large data set, converting INT columns to BIGINT when the data set threatened to grow too large.

ALTER TABLE `owner`DROP FOREIGN KEY `owner_ibfk_1`;ALTER TABLE `thing` CHANGE `id` `id` BIGINT NOT NULL AUTO_INCREMENT;ALTER TABLE `owner` CHANGE `thing_id` `thing_id` BIGINT NOT NULL;UPDATE `thing` SET `id` = CAST(`id` AS UNSIGNED INTEGER);UPDATE `owner` SET `thing_id` = CAST(`thing_id` AS UNSIGNED INTEGER);-- Now the data are BIGINTs; re-create the foreign key constraintALTER TABLE `owner`  ADD CONSTRAINT `owner_ibfk_1` FOREIGN KEY (`thing_id`) REFERENCES `thing` (`id`);