MySQL 8 Administrator???s Guide

Partitioning keys, primary keys, and unique keys

The relationship between partitioning keys with primary keys and unique keys is very important for partition schema structure design. To say the rule in one line it will be that All the columns used in the partitioning in the partition table must include every unique key of the table. So every unique key, including the primary key column on the table, must be part of the partitioning expression. Take a look at the following example for the CREATE TABLE statement using a unique key that does not adhere to the rule:

CREATE TABLE tk1 (    cl1 INT NOT NULL,    cl2 DATE NOT NULL,    cl3 INT NOT NULL,    cl4 INT NOT NULL,    UNIQUE KEY (cl1, cl2))PARTITION BY HASH(cl3)PARTITIONS 4;CREATE TABLE tk2 ( cl1 INT NOT ...