How to change MySQL Primary Key from signed to unsigned?
foreign-keys, innodb, mysql, primary-key, relational-database
Solution
This field is used in foreign key(s). To change this field in MySQL, you should perform these steps:
- Drop all related foreign keys
- Modify field
- Recreate all dropped foreign keys
Problem
In my MySQL InnoDB Database with foreign keys I accidentally made some of my primary keys signed instead of unsigned as I want them to be. Now I want to change it with a ALTER TABLE statement but it does not work: ``` ALTER TABLE `users` CHANGE `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ``` MySQL Error: ``` Error on rename of './db_dev/#sql-478_3' to './db_dev/users' (errno: 150) ``` I don't understand why. I am working with Foreign Keys and tried using a ``` SET foreign_key_checks = 0; ``` Statement before executing the ALTER TABLE from above. Doesn't work either. Notice: All my tables are still empty. There's no data in it yet. Since the Database has a lot of tables it would be much work to drop all the foreign keys and then manually add them again. (if this should be the reason).