partition - InnoDB foreign keys and MySQL partitioning

Question

I am usign MySQL Server version: 5.7.32-0ubuntu0.16.04.1 (Ubuntu). In my DB i have this table with million records:

CREATE TABLE `tableA` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NULL DEFAULT NULL,
  `value` int(11) NOT NULL,
  `tableB_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_sjfet8dx50bhix3ub1dwocpcx` (`timestamp`,`tableB_id`),
  KEY `FK_su2f3awnwvdpq1h3x5x0drjaw` (`tableB_id`),
  CONSTRAINT `FK_su2f3awnwvdpq1h3x5x0drjaw` FOREIGN KEY (`tableB_id`) REFERENCES `tableB` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Is it possible using PARTITIONING by RANGE (in this case by YEAR)?

Is it possible to partition even if a foreign key is present?

Thanks!

question from:https://stackoverflow.com/questions/65888030/innodb-foreign-keys-and-mysql-partitioning

Answer 1

NO.

https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html says:

Foreign keys not supported for partitioned InnoDB tables. Partitioned tables using the InnoDB storage engine do not support foreign keys.

You can't partition this table by year anyway, because every unique key on the table must use every column in the table's partitioning expression.

Read https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations-partitioning-keys-unique-keys.html for details on that.