mysql - 使用alter table添加分区失败

Question

我有一个结构如下的表:

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
)

运行语句添加分区时，失败并显示错误:

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

所以我用 parent_table 删除了外部约束，然后再次运行。它仍然失败并显示相同的错误。

我做错了什么吗？

Answer 1

我知道这是一个老问题，但对于那些因寻找这个问题而落在这里的人来说，因为它是谷歌的第一个结果:

MySQL 不支持分区表上的外键。

来自 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.