CODEWITHSUNDEEP
mysqlforeign-keyspartitioningalter-table
Tu Tran
Tu Tran

Reputation: 1977

Failure in using alter table to add partition

I have a table having structure as 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 running statement to add partition, it fails and show the 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 foreign constraint with parent_table, then run again. It still fails and show the same error.

Did I do anything wrong?

Upvotes: 6

Views: 6398

Answers (2)

Eduardo Sampaio
Eduardo Sampaio

Reputation: 599

I know this is an old question, but for people that fall here from looking for this problem, since its the first Google result:

MySQL does not support foreign keys on partitioned tables.

From the manual

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

  1. No definition of an InnoDB table employing user-defined partitioning may contain foreign key references; no InnoDB table whose definition contains foreign key references may be partitioned.

  2. No InnoDB table definition may contain a foreign key reference to a user-partitioned table; no InnoDB table with user-defined partitioning may contain columns referenced by foreign keys.

Upvotes: 33

Explosion Pills
Explosion Pills

Reputation: 191729

The error is referring to a foreign key on another table that references child_table. You need to find and remove the foreign key from that table, not necessarily child_table. You could also try running SET foreign_key_checks = 0 first.

Upvotes: 0

Related Questions