Problem

A Partition key needs to be part of the Primary key, if not then the following error is raised

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Rationale

Other RDBMS (e.g. Oracle) support the ability to create a Partition Key that is not part of the Primary Key.

Use case:
Financial Transactions. Partition by status (not transaction Id), because the business logic is focused on a sub-set of the transaction status (e.g. "In Progress"). For example, and Order could go "Booking" -> "Allocation" -> "Complete". The majority of the data will be in the terminal state of "Complete", but the business processing will be in the "Booking" and "Allocation" states. Partitioning could mean better performance / latency on a smaller sub-set of data.

Workaround

None (system wise). The application logic could be re-coded to migrate data between tables of the various states.

Reproduce

The example below can be reworked for LIST, LIST COLUMNS, RANGE and RANGE COLUMNS. Each will generate the error reported above.

create table foo (

  id int(10),

  status varchar(1),

  CONSTRAINT foo_pk PRIMARY KEY (id)

)

PARTITION BY LIST COLUMNS (status)

(

  PARTITION a values in ("A"),

  PARTITION b values in ("B"),

  PARTITION c values in ("C"),

  PARTITION d DEFAULT 

);

Solution

Support Partition Keys that are not Primary keys (i.e. remove the current restriction)