26.6.1 Partitioning Keys, Primary Keys, and Unique Keys

26.6.1 Partitioning Keys, Primary Keys, and Unique Keys

이 섹션에서는 파티셔닝 키와 프라이머리 키, 유니크 키 사이의 관계에 대해 설명합니다. 이 관계를 지배하는 규칙은 다음과 같이 표현할 수 있습니다. 파티션된 테이블에 대해 파티셔닝 식에서 사용되는 모든 컬럼은 그 테이블이 가질 수 있는 모든 유니크 키의 일부여야 합니다.

다른 말로 하면, 테이블의 모든 유니크 키는 테이블의 파티셔닝 식에 사용된 모든 컬럼을 사용해야 합니다. (이는 정의상 유니크 키인 테이블의 프라이머리 키도 포함합니다. 이 특정한 경우는 이 섹션의 뒷부분에서 논의합니다.) 예를 들어, 다음의 각 테이블 생성문은 유효하지 않습니다:

1CREATE TABLE t1 (
2    col1 INT NOT NULL,
3    col2 DATE NOT NULL,
4    col3 INT NOT NULL,
5    col4 INT NOT NULL,
6    UNIQUE KEY (col1, col2)
7)
8PARTITION BY HASH(col3)
9PARTITIONS 4;
10
11CREATE TABLE t2 (
12    col1 INT NOT NULL,
13    col2 DATE NOT NULL,
14    col3 INT NOT NULL,
15    col4 INT NOT NULL,
16    UNIQUE KEY (col1),
17    UNIQUE KEY (col3)
18)
19PARTITION BY HASH(col1 + col3)
20PARTITIONS 4;

각 경우에, 정의된 테이블에는 파티셔닝 식에서 사용된 모든 컬럼을 포함하지 않는 유니크 키가 적어도 하나 존재하게 됩니다.

다음의 각 문장은 유효하며, 앞서의 잘못된 테이블 생성문을 동작 가능하게 만드는 한 가지 방법을 나타냅니다:

1CREATE TABLE t1 (
2    col1 INT NOT NULL,
3    col2 DATE NOT NULL,
4    col3 INT NOT NULL,
5    col4 INT NOT NULL,
6    UNIQUE KEY (col1, col2, col3)
7)
8PARTITION BY HASH(col3)
9PARTITIONS 4;
10
11CREATE TABLE t2 (
12    col1 INT NOT NULL,
13    col2 DATE NOT NULL,
14    col3 INT NOT NULL,
15    col4 INT NOT NULL,
16    UNIQUE KEY (col1, col3)
17)
18PARTITION BY HASH(col1 + col3)
19PARTITIONS 4;

다음 예제는 이러한 경우에 발생하는 에러를 보여 줍니다:

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

CREATE TABLE 문장은 col1과 col3 둘 다가 제안된 파티셔닝 키에 포함되어 있지만, 이 컬럼들 중 어느 것도 테이블의 두 유니크 키 모두의 일부가 아니기 때문에 실패합니다. 다음은 잘못된 테이블 정의를 수정하는 한 가지 가능한 방법을 보여 줍니다:

1mysql> CREATE TABLE t3 (
2    ->     col1 INT NOT NULL,
3    ->     col2 DATE NOT NULL,
4    ->     col3 INT NOT NULL,
5    ->     col4 INT NOT NULL,
6    ->     UNIQUE KEY (col1, col2, col3),
7    ->     UNIQUE KEY (col3)
8    -> )
9    -> PARTITION BY HASH(col3)
10    -> PARTITIONS 4;
11Query OK, 0 rows affected (0.05 sec)

이 경우, 제안된 파티셔닝 키인 col3는 두 유니크 키 모두의 일부이므로, 테이블 생성문은 성공합니다.

다음 테이블은 어떤 방식으로도 파티셔닝할 수 없습니다. 두 유니크 키 모두에 속하는 컬럼을 파티셔닝 키에 포함시킬 방법이 없기 때문입니다:

1CREATE TABLE t4 (
2    col1 INT NOT NULL,
3    col2 INT NOT NULL,
4    col3 INT NOT NULL,
5    col4 INT NOT NULL,
6    UNIQUE KEY (col1, col3),
7    UNIQUE KEY (col2, col4)
8);

모든 프라이머리 키는 정의상 유니크 키이므로, 이 제한 사항은 테이블에 프라이머리 키가 있는 경우 그 프라이머리 키에도 적용됩니다. 예를 들어, 다음 두 문장은 유효하지 않습니다:

1CREATE TABLE t5 (
2    col1 INT NOT NULL,
3    col2 DATE NOT NULL,
4    col3 INT NOT NULL,
5    col4 INT NOT NULL,
6    PRIMARY KEY(col1, col2)
7)
8PARTITION BY HASH(col3)
9PARTITIONS 4;
10
11CREATE TABLE t6 (
12    col1 INT NOT NULL,
13    col2 DATE NOT NULL,
14    col3 INT NOT NULL,
15    col4 INT NOT NULL,
16    PRIMARY KEY(col1, col3),
17    UNIQUE KEY(col2)
18)
19PARTITION BY HASH( YEAR(col2) )
20PARTITIONS 4;

두 경우 모두, 프라이머리 키는 파티셔닝 식에서 참조되는 모든 컬럼을 포함하지 않습니다. 그러나 다음 두 문장은 모두 유효합니다:

1CREATE TABLE t7 (
2    col1 INT NOT NULL,
3    col2 DATE NOT NULL,
4    col3 INT NOT NULL,
5    col4 INT NOT NULL,
6    PRIMARY KEY(col1, col2)
7)
8PARTITION BY HASH(col1 + YEAR(col2))
9PARTITIONS 4;
10
11CREATE TABLE t8 (
12    col1 INT NOT NULL,
13    col2 DATE NOT NULL,
14    col3 INT NOT NULL,
15    col4 INT NOT NULL,
16    PRIMARY KEY(col1, col2, col4),
17    UNIQUE KEY(col2, col1)
18)
19PARTITION BY HASH(col1 + YEAR(col2))
20PARTITIONS 4;

테이블에 유니크 키가 전혀 없다면(여기에는 프라이머리 키가 없는 경우도 포함됩니다), 이 제한 사항은 적용되지 않으며, 컬럼 타입이 파티셔닝 타입과 호환되기만 하면 파티셔닝 식에서 어떤 컬럼이든 자유롭게 사용할 수 있습니다.

같은 이유로, 테이블의 파티셔닝 식에서 사용되는 모든 컬럼을 포함하지 않는 한, 나중에 파티션된 테이블에 유니크 키를 추가할 수 없습니다. 다음과 같이 생성된 파티션된 테이블을 생각해 보십시오:

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

다음과 같은 ALTER TABLE 문을 사용하면 t_no_pk에 프라이머리 키를 추가할 수 있습니다:

1#  possible PK
2mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);
3Query OK, 0 rows affected (0.13 sec)
4Records: 0  Duplicates: 0  Warnings: 0
5
6# drop this PK
7mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
8Query OK, 0 rows affected (0.10 sec)
9Records: 0  Duplicates: 0  Warnings: 0
10
11#  use another possible PK
12mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);
13Query OK, 0 rows affected (0.12 sec)
14Records: 0  Duplicates: 0  Warnings: 0
15
16# drop this PK
17mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
18Query OK, 0 rows affected (0.09 sec)
19Records: 0  Duplicates: 0  Warnings: 0

그러나 다음 문장은 실패합니다. 그 이유는 c1이 파티셔닝 키의 일부이지만, 제안된 프라이머리 키의 일부가 아니기 때문입니다:

1#  fails with error 1503
2mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);
3ERROR 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 테이블을 생각해 보십시오:

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

다음 ALTER TABLE 문은 에러와 함께 실패하는데, 그 이유는 added 컬럼이 테이블의 어떤 유니크 키에도 속하지 않기 때문입니다:

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

그러나 다음과 같이 파티셔닝 컬럼으로 id 컬럼을 사용하는 문장은 유효합니다:

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

np_pk의 경우, 파티셔닝 식의 일부로 사용할 수 있는 컬럼은 id뿐입니다. 파티셔닝 식에서 다른 컬럼이나 여러 컬럼을 사용하여 이 테이블을 파티셔닝하려면, 먼저 테이블을 수정해서 원하는 컬럼(들)을 프라이머리 키에 추가하거나, 프라이머리 키를 완전히 제거해야 합니다.