Skip to content

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

ERROR 1503 (HY000)

Tried to partition a table, but it failed with ERROR 1503 (HY000).

Primary Key

mysql> ALTER TABLE orders PARTITION BY RANGE(unix_timestamp(order_time)) (PARTITION p2013 VALUES LESS THAN (1388505600) ) ;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

ERROR 1503 (HY000) indicates that the primary key must contain the partition key. So the solution is to add this column to the primary key.

mysql> ALTER TABLE orders DROP PRIMARY KEY, ADD PRIMARY KEY (id, order_time);
Query OK, 12 rows affected (0.03 sec)
Records: 12  Duplicates: 0  Warnings: 0

Unique Key

mysql> ALTER TABLE orders PARTITION BY RANGE(unix_timestamp(order_time)) (PARTITION p2013 VALUES LESS THAN (1388505600) ) ;
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

The error indicates all the unique index must contain the partition key.

Add this column to all the unique indexes.

mysql> ALTER TABLE orders DROP INDEX order_id_UNIQUE , ADD UNIQUE INDEX order_id_UNIQUE (id, order_time) USING BTREE ;
Query OK, 12 rows affected (0.03 sec)
Records: 12  Duplicates: 0  Warnings: 0

We're good.

Leave a Reply

Your email address will not be published. Required fields are marked *