ERROR 1217 (23000)
Got a MySQL ERROR 1217 (23000) when tried to partition a table.
mysql> alter table geolocations partition by ...
...
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Let's see what it's been said in the official documentation. MySQL :: MySQL 8.0 Reference Manual :: 22.6 Restrictions and Limitations on Partitioning
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:
- 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.
- 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.
Which means, we cannot have any foreign keys in a partitioned table either when creating or altering. We have to drop the foreign keys before partitioning.
More about add a foreign key constraint, you can refer to this post: How to Resolve MySQL ERROR 1215 (HY000): Cannot add foreign key constraint.