Partitioning tables with foreign key MYSQL

Question

I get the below error while trying to partition a table on mysql, does this mean a table cannot be partitioned if it's referencing foreign keys?

Foreign keys are not yet supported in conjunction with partitioning

• Rick James: Don't use partitioning. It rarely provides any benefit. Would you care to provide SHOW CREATE TABLE? With that, I can elaborate.

Answer 1

Yes, it is one of the many restrictions on partitioned InnoDB tables.

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.

The scope of the restrictions just listed includes all tables that use the InnoDB storage engine. CREATE TABLE and ALTER TABLE statements that would result in tables violating these restrictions are not allowed.

see other restrictions

You will need to program the foreign keys constraints in triggers and/or application level.

• Dera: Thanks for the response.

Answer 2

This article from Oracle has what looks like a really cool workaround where foreign keys on a partitioned table are implemented via an intermediate non-partitioned table with TRIGGER statements attached to it: https://blogs.oracle.com/mysql/post/how-to-partition-tables-with-foreign-keys-in-mysql-heatwave-with-a-workaround

• mustaccio: You may want to add the salient point from your link to your answer itself, lest it becomes useless when the link goes stale.