Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.2Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


MySQL 5.6 リファレンスマニュアル  /  ...  /  パーティショニングキー、主キー、および一意キー

19.6.1 パーティショニングキー、主キー、および一意キー

このセクションでは、パーティショニングキーと主キーおよび一意キーとの関係について説明します。この関係を制御するルールは次のように表現できます。パーティション化されたテーブルのパーティショニング式で使用されるすべてのカラムは、テーブルが持つことができるすべての一意キーの一部である必要があります。

つまり、テーブルのすべての一意キーは、テーブルのパーティショニング式内のすべてのカラムを使用する必要があります(これには、テーブルの主キーも含まれます (自明で一意キーであるため)。この点については、このセクションで後述します)。たとえば、次の各テーブル作成ステートメントは無効です。

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1),
    UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

どちらの場合も、記述されたテーブルには、パーティショニング式に使用されているすべてのカラムを含んでいない一意キーが少なくとも 1 つあります。

次の各ステートメントは有効で、対応する無効なテーブル作成ステートメントを機能させる 1 つの方法を示しています。

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

次の例は、そのような場合に生成されるエラーを示しています。

mysql> CREATE TABLE t3 (
    ->     col1 INT NOT NULL,
    ->     col2 DATE NOT NULL,
    ->     col3 INT NOT NULL,
    ->     col4 INT NOT NULL,
    ->     UNIQUE KEY (col1, col2),
    ->     UNIQUE KEY (col3)
    -> )
    -> PARTITION BY HASH(col1 + col3)
    -> PARTITIONS 4;
ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

この CREATE TABLE ステートメントは、指定されたパーティショニングキーに col1 および col3 が含まれているけれども、これらのカラムのいずれもテーブルの両方の一意キーの一部でないために、失敗します。無効なテーブル定義の考えられる解決策の 1 つを次に示します。

mysql> CREATE TABLE t3 (
    ->     col1 INT NOT NULL,
    ->     col2 DATE NOT NULL,
    ->     col3 INT NOT NULL,
    ->     col4 INT NOT NULL,
    ->     UNIQUE KEY (col1, col2, col3),
    ->     UNIQUE KEY (col3)
    -> )
    -> PARTITION BY HASH(col3)
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.05 sec)

この場合、指定されたパーティショニングキー col3 は両方の一意キーの一部であるため、このテーブル作成ステートメントは成功します。

次のテーブルは、両方の一意キーに属するカラムをパーティショニングキーに含めることができないため、パーティション化できません。

CREATE TABLE t4 (
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3),
    UNIQUE KEY (col2, col4)
);

すべての主キーは自明で一意キーであるため、この制約にはテーブルの主キーも含まれます (ある場合)。たとえば、次の 2 つのステートメントは無効です。

CREATE TABLE t5 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t6 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col3),
    UNIQUE KEY(col2)
)
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;

どちらの場合も、パーティショニング式で参照されるすべてのカラムが主キーに含まれていません。ただし、次の 2 つのステートメントは両方とも有効です。

CREATE TABLE t7 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;

CREATE TABLE t8 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2, col4),
    UNIQUE KEY(col2, col1)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;

テーブルに一意キーがない場合 (主キーがない場合を含む) はこの制約は適用されず、カラム型がパーティショニングタイプと互換性があるかぎり、パーティショニング式に任意のカラムを使用できます。

同じ理由で、テーブルのパーティショニング式で使用されるすべてのカラムが一意キーに含まれている場合を除き、パーティション化されたテーブルにあとから一意キーを追加することはできません。次のように作成されたパーティション化されたテーブルがあるとします。

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)

次のいずれかの ALTER TABLE ステートメントを使用することで、t_no_pk に主キーを追加できます。

#  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

ただし、次のステートメントは失敗します。c1 は、パーティショニングキーの一部ですが、指定された主キーの一部ではないためです。

#  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

t_no_pk のパーティショニング式は c1 のみであるため、c2 のみに一意キーを追加しようとすると失敗します。ただし、c1 および c2 の両方を使用する一意キーは追加できます。

これらのルールは、既存のパーティション化されていないテーブルを ALTER TABLE ... PARTITION BY を使用してパーティション化するときにも適用されます。次のように作成されたテーブル np_pk があるとします。

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)

次の ALTER TABLE ステートメントは、added カラムがテーブルの一意キーの一部でないため、エラーで失敗します。

mysql> ALTER TABLE np_pk
    ->     PARTITION BY HASH( TO_DAYS(added) )
    ->     PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

ただし、次に示すように、パーティショニングカラムに id カラムを使用する次のステートメントは有効です。

mysql> ALTER TABLE np_pk
    ->     PARTITION BY HASH(id)
    ->     PARTITIONS 4;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

np_pk の場合、パーティショニング式の一部として使用できる唯一のカラムは id です。このテーブルをパーティショニング式でほかのカラムを使用してパーティション化する場合は、目的のカラムを主キーに追加するか、主キーをすべて削除することによって、まずテーブルを変更する必要があります。


User Comments
  Posted by Adrian Corston on October 18, 2007
If you have a table with an auto_increment column and you want to partition on a date column in that table, make sure you put both of those columns in your primary key like this:

CREATE TABLE stuff (id INT AUTO_INCREMENT, d DATE, PRIMARY KEY (id, d))
PARTITION BY RANGE(to_days(d)) (PARTITION p0 VALUES LESS THAN (733332), PARTITION p1 VALUES LESS THAN MAXVALUE);

This may seem to be stating the obvious and repeating what's written above, but I used to (incorrectly) believe that you had to create a primary key on just your auto_increment column, which would have made it impossible to partition on the date column as shown above.

5.1.22-rc
  Posted by Peter Colclough on November 17, 2011
This doesn't work, as it enables duiplicates of your auto_uncrement to exist, so long as they aren't at the same time. Oterwise it woudl have solved my issue... thanks for trying
  Posted by Trey Raymond on February 1, 2012
While the key would not prevent duplicates, auto_increment locking done by innodb would. The only exception is if you explicitly set the id column...but that is usually not done where it has auto_increment set - the point is that the database generates the value for you.
  Posted by David Wilkins on February 14, 2012
ALTER TABLE whatever
DROP PRIMARY KEY,
ADD PRIMARY KEY (`key1`, `key2`),
ADD UNIQUE INDEX `key1_UNIQUE` (`key1` ASC);

There you go, primary key on two fields but preserving uniqueness of one. Not that hard.

  Posted by Anne Mouse on March 31, 2012
Posted by David Wilkins on February 14 2012 11:09pm [Delete] [Edit]
ALTER TABLE whatever
DROP PRIMARY KEY,
ADD PRIMARY KEY (`key1`, `key2`),
ADD UNIQUE INDEX `key1_UNIQUE` (`key1` ASC);

There you go, primary key on two fields but preserving uniqueness of one. Not that hard.

Actually, it is that hard because of:
In other words, every unique key on the table must use every column in the table's partitioning expression.

So you CANNOT add key1_UNIQUE to a table that you want to partition on any other field.
Sign Up Login You must be logged in to post a comment.