Cannot change primary key because of "incorrectly formed foreign key constraint" error

Question

I have a table which has the below schema definition :

CREATE TABLE `currency` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` char(3) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
  PRIMARY KEY (`id`),
  UNIQUE KEY `code_UNIQUE` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

What I want is to drop the id column and make code as the new primary key. And some of the other tables are having foreign keys to this table. I tried the below command but failed:

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE `currency` CHANGE COLUMN `id` `id` INT(11) NOT NULL, DROP PRIMARY KEY;
ALTER TABLE currency ADD PRIMARY KEY (code);
SET FOREIGN_KEY_CHECKS=1;

MySQL throws the below exception:

[ERROR in query 2] Error on rename of './db/#sql-849_1' to './db/currency' (errno: 150 - Foreign key constraint is incorrectly formed) Execution stopped!

Answer 1

Running

ALTER TABLE myTable DROP PRIMARY KEY;

caused an error like

`Error Code: 1025. Error on rename of 'some_name' to 'another_name' (errno: 150 - Foreign key constraint is incorrectly formed)`

Dropping, creating new column and adding it as a primary key all as a single command works like a charm.

Even though I don't know the root cause, this is the final solution I came up to:

-- Suppose c1 and c2 are a composite primary key and 
-- I want to add an incremental primary key named id
ALTER TABLE myTable 
    DROP PRIMARY KEY, 
    ADD id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST, 
    ADD INDEX `an_index_name_for_c1_c2` (`c1`, `c2`);

Note that for sake of legacy code performance, I add previous compound primary key columns as a new compound index.

Answer 2

The error

Error on rename of ... errno: 150 - Foreign key constraint is incorrectly formed)

happens because you are trying to drop a referenced primary key, even though you are disabling foreign key constraint checking with SET FOREIGN_KEY_CHECKS=0;

Disabling foreign key checks would allow you to temporarily delete a row in the currency table or add an invalid currencyId in the foreign key tables, but not to drop the primary key.

Changing a PRIMARY KEY which is already referenced by other tables isn't going to be simple, since you risk losing referential integrity between the tables and losing the relationship between data. In order to preserve the data, you'll need a process such as:

• Add a new Foreign key column (code) to each FK table
• Map the code foreign key from the previous currencyId via an update
• Drop the existing foreign key
• Drop the old currencyId foreign key column
• Once all FK's have been dropped, change the primary key on the currency table
• Reestablish the foreign keys based on the new code column

The below would do this without needing to disable the FOREIGN_KEY_CHECKS, but the foreign key map / drop / recreate step would need to be repeated for all tables referencing currency:

-- Add new FK column
ALTER TABLE FKTable ADD currencyCode char(3) 
                        CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
-- Map FK column to the new Primary Key
UPDATE FKTable
  SET currencyCode = (SELECT `code` FROM currency WHERE id = FKTable.currencyId);
-- Drop the old foreign key + column
ALTER TABLE FKTable DROP FOREIGN KEY FKTable_Currency;
ALTER TABLE FKTable DROP COLUMN currencyId;
-- Once the above is done for all FK tables, drop the PK on currency
ALTER TABLE `currency` CHANGE COLUMN `id` `id` INT(11) NOT NULL, 
                                                      DROP PRIMARY KEY;
ALTER TABLE currency ADD PRIMARY KEY (`code`);
ALTER TABLE FKTable ADD CONSTRAINT FKTable_Currency2
          FOREIGN KEY (currencyCode) REFERENCES currency(`code`);

SqlFiddle here