Bug #19237 LIST AND RANGE Partition: Can't have primary key when partitioning by non-PK
Submitted: 21 Apr 2006 0:43 Modified: 25 Apr 2006 3:06
Reporter: Brian Lucas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.1.10-BK, 5.1.7beta OS:Linux (Linux)
Assigned to: Jon Stephens CPU Architecture:Any

[21 Apr 2006 0:43] Brian Lucas
Description:
Using example provided (http://dev.mysql.com/doc/refman/5.1/en/partitioning-range.html), one can't have a primary or unique key when partitioning by a non-primary key by RANGE or LIST.

How to repeat:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

SQL query:

ALTER TABLE `employees` ADD PRIMARY KEY ( `id` );

MySQL said: Documentation
#1481 - A PRIMARY KEY need to include all fields in the partition function 

Suggested fix:
Is this a limitation or expected behavior?  Seems like bad design if one can't add or use a primary key or unique in this instance.
[24 Apr 2006 13:37] Valeriy Kravchuk
Thank you for a problem report. Verified just as described with 5.1.10-BK on Linux:

mysql> CREATE TABLE employees (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job_code INT NOT NULL,
    ->     store_id INT NOT NULL
    -> )
    -> PARTITION BY RANGE (store_id) (
    ->     PARTITION p0 VALUES LESS THAN (6),
    ->     PARTITION p1 VALUES LESS THAN (11),
    ->     PARTITION p2 VALUES LESS THAN (16),
    ->     PARTITION p3 VALUES LESS THAN (21)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE `employees` ADD PRIMARY KEY ( `id` );
ERROR 1482 (HY000): A PRIMARY KEY need to include all fields in the partition function.

If it is intended behaviour, it should be properly documented (at http://dev.mysql.com/doc/refman/5.1/en/partitioning-range.html).
[24 Apr 2006 20:50] Mikael Ronström
This is intended behaviour so I turn it into a Docs bug and assign it to Jon and change lead
accordingly
[25 Apr 2006 0:58] Brian Lucas
Mikael, thanks for the comment.  I would love to have this function available.  Without generalizing too much, is it still a long ways off before this capability is enabled?  

Speaking for just myself, the usefulness of such a great feature like partitioning is significantly diminished if I can't have a primary or unique key in this capacity.    I'm looking forward to seeing it made available.
[25 Apr 2006 3:06] Jon Stephens
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

This a consequence of the following (already documented) limitation:

"If a table that is to be partitioned has a primary key, then any columns used in the partitioning expression must be part of the primary key."

The issue is *not* that you can't have a primary or unique key on a partitioned table, nor that you can't add one later using ALTER TABLE.

However, if such a key is defined for the table when it is created, then it must use all columns that make up the partitioning key. Nor can you later add a PK or UK that doesn't meet this requirement. 

However, you *can* add a PK or UK, provided that it includes all columns used for the partitioning key:

mysql> CREATE TABLE t5 (c1 INT, c2 INT) 
     > PARTITION BY RANGE(c1) (
     > PARTITION p0 VALUES LESS THAN (10), 
     > PARTITION p1 VALUES LESS THAN (20)
     > );
Query OK, 0 rows affected (0.10 sec)

mysql> ALTER TABLE t5 ADD PRIMARY KEY (c1);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

See http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html (which I've updated with a lengthier version of this example/explanation to make this clearer).