Partitioning tables with foreign key MYSQL

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.

Method 1

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:

  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 rrstrictions

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply