Cannot partitioning table on mysql

Question

When I run alter table for partitioning on innodb table I got this error on Mysql 5.7:

Foreign keys are not yet supported in conjunction with partitioning

knowing that I already drop foreign key of this table, and drop foreign key where the id of this table is.

here is my table schema now:

| record | CREATE TABLE `record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `altitude` smallint(6) DEFAULT NULL,
  `counter` double DEFAULT NULL,
  `datetime` datetime NOT NULL,
  `details` longtext,
  `deviation` smallint(6) DEFAULT NULL,
  `engine` tinyint(1) DEFAULT NULL,
  `event` smallint(6) DEFAULT NULL,
  `fuel_level` double DEFAULT NULL,
  `gsm_level` int(11) DEFAULT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `satellite` smallint(6) DEFAULT NULL,
  `speed` smallint(6) DEFAULT NULL,
  `time` time NOT NULL,
  `treated` tinyint(1) NOT NULL DEFAULT '0',
  `id_driver` bigint(20) DEFAULT NULL,
  `id_vehicle_device` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_ai9y9d52lx1xvtaiv3w0x60a5` (`id_driver`),
  KEY `FK_ilwlg0mt87bxyemx4tov1elni` (`id_vehicle_device`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Answer 1

From Restrictions and Limitations on Partitioning:

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.

Answer 2

Maybe you can find missed referenced or referring table by this SQL

WITH t AS (
SELECT  TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME,
        GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION) AS COLUMN_NAMES,
        MIN(REFERENCED_TABLE_NAME) AS REFERENCED_TABLE_NAME,
        GROUP_CONCAT(REFERENCED_COLUMN_NAME ORDER BY POSITION_IN_UNIQUE_CONSTRAINT) AS REFERENCED_COLUMN_NAMES
FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE   REFERENCED_TABLE_NAME IS NOT NULL
        AND TABLE_SCHEMA = <your_schema>
GROUP BY
        TABLE_NAME, CONSTRAINT_NAME
ORDER BY
        TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
SELECT  *
FROM    t
WHERE   table_name = 'record' OR referenced_table_name = 'record'

Answer 3

This means that no user-partitioned InnoDB table may contain foreign key references or columns referenced by foreign key