All we need is an easy explanation of the problem, so here it is.
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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Yes it is one of the many restriction
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.
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.
You will need to program the foreign keys constraints in triggers and/or application level
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂