Geek Answers Handbook

Error using alter table to add partition

I have a table with a structure as shown below:

CREATE TABLE `child_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `value` int, `ref_id` int, PRIMARY KEY (`id`), KEY `ref_id` (`ref_id`), CONSTRAINT `FK4E9BF08E940F8C98` FOREIGN KEY (`ref_id`) REFERENCES `parent_table` (`id`) ON DELETE CASCADE )

When the operator starts to add a section, it crashes and shows an error:

 ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails SQL Statement: ALTER TABLE `learning`.`child_table` PARTITION BY HASH(ref_id) PARTITIONS 10

So, I remove the outer constraint with parent_table and then run again. It still does not work and shows the same error.

Did I do something wrong?

+4
source share
2 answers

I know this is an old question, but for people who get here from searching for this problem, starting with the first Google result:

MySQL does not support foreign keys in partitioned tables.

From the manual

Foreign keys are not supported for InnoDB partitioned tables. Partitioned tables using the InnoDB storage engine do not support foreign keys. In particular, this means that the following two statements are true:

  • No InnoDB table definition using user-defined partitioning can contain foreign key references; An InnoDB table whose definition contains foreign key references can be partitioned.

  • No table definition InnoDB may contain a foreign key reference for a user-shared table; no custom partitioned InnoDB table can contain columns referenced by foreign keys.

+23
source

The error refers to a foreign key in another table that refers to child_table . You need to find and remove the foreign key from this table, not necessarily child_table . You can also try running SET foreign_key_checks = 0 .

+1
source

More articles:


All Articles