2

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

1
  • Don't use partitioning. It rarely provides any benefit. Would you care to provide SHOW CREATE TABLE? With that, I can elaborate.
    – Rick James
    Aug 16, 2021 at 0:25

1 Answer 1

3

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.

1
  • Thanks for the response.
    – Dera
    Aug 15, 2021 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.