How to change MySQL Primary Key from signed to unsigned?


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).



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

Mysql Tutorials

Mysql Howto..

How to know if command line MySQL database import has crashed?

For the following mysql query I got an output. But how to modify the query so that I get results for a range of date

How to check if email exist while typing in MYSQL using jQuery, AJAX and CodeIgniter

How do I find which transaction is causing a “Waiting for table metadata lock” state?

How to display the output or error mysql query from perl to the same webpage?

How to do random ordering in MySQL. Should I use random() or uuid()

How to import Production Data from Development Database?

How to create Trigger to prevent inserting the duplicate row in MYSQL 5.0.27?

how to get the hierarchical menu from mysql

How can I determine which type of database is this?

How to find rows with only special characters on a field using MySQL?

mysql workbench md5 column - how to?

How to search for record in mysql using php?

How to pull mysql database from heroku to local machine

How to insert two records into different tables first one with insert and then with select from the insert php mysql

Mysql - How to retrieve fields from two tables based on certain criteria?

How to plot real time graph using php and AJAX?

Searched for how to CONCATENATE several parts to one SELECT using IN and OUT parameters

How do I get the values returned with Hibernate?

How can I add an implied year to a string which does not specify the year?

how can i create a tagging system using php and mysql?

how to structure a mysql subquery

PHP / MySQL: How to compare selected value with input received via $_POST

How to order values in ascending order from MySQL query with PHP?

How to catch error 1062 “duplicate entry” independent from used database/engine?

How to integrate Spring with JDBC?

How to set environment variables for Laravel 5 on AWS EC2 with MySQL

How to check for existence of row in a joined table - MySQL

Dynamic JavaScript slideshow

How to Backup and Restore a MySQL Database using NetBeans?