How to change MySQL Primary Key from signed to unsigned?

Question

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

Answer 1

I took over a project, which had an identical issue of some primary keys been signed, and the related foreign field was also signed.

I used @Devart approach, but I was able to automate the entire process. I was able to query the information_schema to generate additional SQL statements, which I could "cut and paste" and then later run.

Generate SQL statements to drop all constraints

SELECT concat('ALTER TABLE ', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';')
FROM information_schema.key_column_usage
WHERE CONSTRAINT_SCHEMA = 'YOUR_SCHEMA_NAME'
AND referenced_table_name IS NOT NULL;

Alter any id columns which need to be changed to UNSIGNED

SELECT
  CONCAT('ALTER TABLE `', TABLE_NAME, '` CHANGE COLUMN  id id INT UNSIGNED NOT NULL AUTO_INCREMENT;')
FROM `COLUMNS`
WHERE
  `COLUMN_KEY` = 'PRI' AND
  `TABLE_SCHEMA` = 'YOUR_SCHEMA_NAME' AND
  `COLUMN_TYPE` NOT LIKE '%unsigned%' AND
  `COLUMN_TYPE` LIKE '%int%' AND
  `COLUMN_NAME` = 'id';

Alter foreign fields pointing to the id

SELECT CONCAT('ALTER TABLE `', kcu.TABLE_NAME, '` CHANGE COLUMN  ', kcu.COLUMN_NAME,' ', kcu.COLUMN_NAME, ' INT UNSIGNED ', IF(c.IS_NULLABLE = 'YES', 'NULL', 'NOT NULL'), ';')
FROM `KEY_COLUMN_USAGE`  kcu
INNER JOIN `COLUMNS` c
ON
  kcu.TABLE_NAME = c.TABLE_NAME AND
  kcu.COLUMN_NAME = c.COLUMN_NAME
WHERE
  `REFERENCED_COLUMN_NAME` = 'id' AND
  `REFERENCED_TABLE_NAME` IN (
    SELECT
        TABLE_NAME
    FROM `COLUMNS`
    WHERE
        `COLUMN_KEY` = 'PRI' AND
        `TABLE_SCHEMA` = 'YOUR_SCHEmA_NAME' AND
        `COLUMN_TYPE` NOT LIKE '%unsigned%' AND
        `COLUMN_TYPE` LIKE '%int%' AND
        `COLUMN_NAME` = 'id'
);

(Note in this statement, all are altered to be UNSIGNED by mistake even if they are already UNSIGNED but this does not cause any problems)

Reinsert all the required constraints

SELECT CONCAT('ALTER TABLE ', rc.`TABLE_NAME` ,' ADD CONSTRAINT ', rc.`CONSTRAINT_NAME`, ' FOREIGN KEY (',kcu.`COLUMN_NAME`,') REFERENCES ', rc.`REFERENCED_TABLE_NAME` ,'(id) ON DELETE ', DELETE_RULE , ' ON UPDATE ' , UPDATE_RULE, ';')
FROM `REFERENTIAL_CONSTRAINTS` rc
INNER JOIN
    `KEY_COLUMN_USAGE` kcu
     ON rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE kcu.CONSTRAINT_SCHEMA = 'api'      AND
kcu.`REFERENCED_COLUMN_NAME` = 'id';

Pay close attention to these SQL statements, it might need to be modified for your schema, for example, it assumes your primary id is called "id".

Also, you must run all 4 of these statements, BEFORE running any of their actual output.

Using MySQL 5.6

Answer 2

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

Answer 3

Take a dump of the database using mysql command mysqldump and search and add unsigned every place where required. Then restore the dump to same database. Before restore delete all the tables from that and dump it.