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: | |
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
[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).