今天主要介绍下面这几个错误:
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
主键必须包括表的分区函数中的所有列,一个惟一的索引必须包括表的分区函数中的所有列,是不是不太好理解意思
其实就是这么个意思:表上的每一个唯一性索引必须用于分区表的表达式上(其中包括主键索引)
下面我来举几个例子:CREATE TABLE t1 (
id INT NOT NULL,
aid DATE NOT NULL,
bid INT NOT NULL,
PRIMARY KEY (id)
)
PARTITION BY KEY(bid)
PARTITIONS 10;
[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning functionCREATE TABLE t1 (
id INT NOT NULL,
aid DATE NOT NULL,
bid INT NOT NULL,
UNIQUE KEY (id)
)
PARTITION BY KEY(bid)
PARTITIONS 10;
[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning functionCREATE TABLE t1 (
id INT NOT NULL,
aid DATE NOT NULL,
bid INT NOT NULL,
PRIMARY KEY (id,bid)
)
PARTITION BY KEY(bid)
PARTITIONS 10;
Query OK, 0 rows affected (0.535 sec)
CREATE TABLE t1 (
id INT NOT NULL,
aid DATE NOT NULL,
bid INT NOT NULL,
UNIQUE KEY (id,bid)
)
PARTITION BY KEY(bid)
PARTITIONS 10;
Query OK, 0 rows affected (0.625 sec)
这一组例子说明了如果要用bid作为分区key,则bid至少要包含在主键或者唯一键。
再来看一组例子:CREATE TABLE t1 (
id INT NOT NULL,
aid DATE NOT NULL,
bid INT NOT NULL,
PRIMARY KEY (id,aid),
UNIQUE KEY (bid)
)
PARTITION BY KEY(bid)
PARTITIONS 10;
[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE t1 (
id INT NOT NULL,
aid DATE NOT NULL,
bid INT NOT NULL,
PRIMARY KEY (id,aid),
UNIQUE KEY (bid)
)
PARTITION BY KEY(aid)
PARTITIONS 10;
[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function
这2个分区表是无法建立的,因为没有一个分区键,可以同时属于两个唯一性索引的键。
下面再来看一组例子:
CREATE TABLE t1 (
id INT NOT NULL,
aid DATE NOT NULL,
bid INT NOT NULL,
PRIMARY KEY (id,aid,bid)
)
PARTITION BY KEY(bid)
PARTITIONS 10;
Query OK, 0 rows affected (0.475 sec)
CREATE TABLE t1 (
id INT NOT NULL,
aid DATE NOT NULL,
bid INT NOT NULL,
UNIQUE KEY (id,aid,bid)
)
PARTITION BY KEY(bid)
PARTITIONS 10;
Query OK, 0 rows affected (0.532 sec)
CREATE TABLE t1 (
id INT NOT NULL,
aid DATE NOT NULL,
bid INT NOT NULL,
PRIMARY KEY (id,aid,bid),
UNIQUE KEY (bid)
)
PARTITION BY KEY(bid)
PARTITIONS 10;
Query OK, 0 rows affected (0.526 sec)
这三个都建立分区表成功了,因为bid同时属于一个或以上的唯一性索引的键
通过上面这些例子,大家可以深入的理解Mysql分区中分区键和唯一索引主键的关系了,希望对大家有所帮助。
打赏
微信扫一扫,打赏作者吧~