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

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 columnALTER TABLE FKTable ADD currencyCode char(3)                         CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;-- Map FK column to the new Primary KeyUPDATE FKTable  SET currencyCode = (SELECT `code` FROM currency WHERE id = FKTable.currencyId);-- Drop the old foreign key + columnALTER 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 currencyALTER 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

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

Why not

• drop foreign key
• drop primary key
• create primary key
• create foreign key

?

I have to admit I have not much traffic right now (none) and just test data that could be easily recovered, but it worked just fine for me.