14

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

Norwald2
  • 695
  • 4
  • 9
  • 19

6 Answers6

11

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
Devart
  • 119,203
  • 23
  • 166
  • 186
  • Yes, it is. You can try column rename with refactoring in [dbForge Studio for MySQL](http://www.devart.com/dbforge/mysql/studio/); it will do it automatically (in Database Explorer). – Devart Jun 14 '12 at 13:18
  • I used this approach and scripted the entire process, see answer https://stackoverflow.com/a/47169383/1241791 – Sam Anthony Nov 08 '17 at 00:10
7

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

Sam Anthony
  • 1,669
  • 2
  • 22
  • 39
6

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.

Rohith
  • 429
  • 4
  • 7
2

Try this
ALTER TABLE 'users' MODIFY 'id' UNSIGNED NOT NULL AUTO_INCREMENT'

Gaurav Gandhi
  • 3,041
  • 2
  • 27
  • 40
0

MySQL Workbench will let you do this. You need to ensure you change the PRIMARY KEY and all of the columns in each and every table that has a foreign key reference to it.

I found it to work on a number of scenarios, YMMV.

Ian
  • 402
  • 2
  • 16
0

The following will work in many circumstances although it depends on how the column is being modified

-- disable fk constraints
SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE 'table' MODIFY 'column'....

-- re-enable fk constraints
SET FOREIGN_KEY_CHECKS=1;

This may not work if it makes the FKs invalid - in which case you'll need to drop/recreate the FKs.

ALTER TABLE 'table' DROP FOREIGN KEY FK_xxxxxxxxxxx;

ALTER TABLE 'table' MODIFY 'column'....

ALTER TABLE 'table'
  add constraint FK_xxxxxxxxxxx
foreign key ('key-column') references 'other table' ('key-column');
exception
  • 569
  • 7
  • 20