SQL 错误代码:1503 A UNIQUE INDEX must include all columns in the table's partitioning function

阅读(3142)

SQL中包含PARTION BY语句时,遇到如下错误:

错误代码: 1503
A UNIQUE INDEX must include all columns in the table's partitioning function

原因在于:PARTITION BY语句中的字段,需要在所有的UNIQUE KEY和PRIMARY KEY定义中有涉及。注意这里的限定词:所有。如果定义了多个UNIQUE KEY,那么所有的UNIQUE KEY组合中都必须包含有PARTITION的字段。

举例:


CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY (col1, col3),
    UNIQUE KEY (col1, col2, col3),
    UNIQUE KEY (col3, col4)    // 如果此行不包含col3,那么就会报本文的错误
)
PARTITION BY HASH(col3);


官方详细解释如下:

The rule governing this relationship can be expressed as follows: All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.

In other words, every unique key on the table must use every column in the table's partitioning expression. (This also includes the table's primary key, since it is by definition a unique key. This particular case is discussed later in this section.)

via:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-keys.html