If a table has no unique keys—this includes having no primary key—then this restriction does not apply, and you may use any column or columns in the partitioning expression as long as the column type is compatible with the partitioning type. 

For the same reason, you cannot later add a unique key to a partitioned table unless the key includes all columns used by the table’s partitioning expression. Consider the partitioned table created as shown here: 

mysql> CREATE TABLE t_no_pk (c1 INT, c2 INT) -> PARTITION BY RANGE(c1) ( -> PARTITION p0 VALUES LESS THAN (10), -> PARTITION p1 VALUES LESS THAN (20), -> PARTITION p2 VALUES LESS THAN (30), -> PARTITION p3 VALUES LESS THAN (40) -> ); Query OK, 0 rows affected (0.12 sec) 

It is possible to add a primary key to t_no_pk  using either of these ALTER TABLE  statements: 

# possible PK mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 # drop this PK mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 # use another possible PK mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2); Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 # drop this PK mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 

However, the next statement fails, because c1  is part of the partitioning key, but is not part of the proposed primary key: 

# fails with error 1503 mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2); ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function 

Since t_no_pk  has only c1  in its partitioning expression, attempting to adding a unique key on c2  alone fails. However, you can add a unique key that uses both c1  and c2 . 

These rules also apply to existing nonpartitioned tables that you wish to partition using ALTER TABLE ... PARTITION BY . Consider a table np_pk  created as shown here: 

mysql> CREATE TABLE np_pk ( -> id INT NOT NULL AUTO_INCREMENT, -> name VARCHAR(50), -> added DATE, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.08 sec) 

The following ALTER TABLE  statement fails with an error, because the added  column is not part of any unique key in the table: 

mysql> ALTER TABLE np_pk -> PARTITION BY HASH( TO_DAYS(added) ) 

