Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 576
Tu Tran Avatar asked Mar 29 '13 04:03

Tu Tran


2 Answers

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.

like image 110
Eduardo Sampaio Avatar answered Oct 16 '22 09:10

Eduardo Sampaio


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.

like image 45
Explosion Pills Avatar answered Oct 16 '22 09:10

Explosion Pills