Geek Answers Handbook

Unable to change primary key due to "incorrectly generated foreign key" error

I have a table that has the following 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;

I want to leave the id column and make code as the new primary key. And some other tables have foreign keys to this table. I tried the command below, but could not:

 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 following exception:

[ERROR in request 2] Error renaming './db/#sql-849_1' to './db/currency' (errno: 150 - foreign key constraint is incorrectly generated) Execution stopped!

+6
source share
2 answers

Error

Error renaming ... errno: 150 - foreign key constraint is not correctly formed)

happens because you are trying to delete the referenced primary key, even if you disable the check of foreign key constraints with SET FOREIGN_KEY_CHECKS=0;

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

Changing the PRIMARY KEY that other tables are already referencing will not be easy, since you risk losing the referential integrity between the tables and losing the connection between the data. To save data, you need a process, for example:

  • Add a new foreign key column ( code ) to each FK table
  • Match the foreign key code with the previous currencyId using update
  • Discard existing foreign key
  • Discard old column currencyId
  • After all FKs have been reset, change the primary key in the currency table
  • Recover foreign keys based on new code column

This will be done below without having to disable FOREIGN_KEY_CHECKS , but the foreign key step / drop / rereate must be repeated for all tables that reference 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

+9
source

Performance

 ALTER TABLE myTable DROP PRIMARY KEY;

caused an error for example

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

Removing, creating a new column and adding it as the primary key all as one command works like a charm.

Despite the fact that I do not know the root cause, this is the final decision I came 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 the performance of legacy codes, I am adding the previous composite primary key columns as a new composite index.

+3
source

More articles:


All Articles