CODEWITHSUNDEEP
mysqlsqlindexingdatabase-partitioning
aarbor
aarbor

Reputation: 1534

MySQL - A UNIQUE INDEX must include all columns in the table's partitioning function

I have the following table setup (column names simplified for the example):

CREATE TABLE data_2016
( `a` INTEGER , 
  `b` INTEGER,
  `c` VARCHAR(255),
  `d` BIGINT,
  `e` VARCHAR(255) NOT NULL,
  `f` INTEGER ,
  `g` BIGINT ,
  `h` BIGINT ,
  `i` SERIAL,
PRIMARY KEY (`d`,`i`),
UNIQUE KEY(`b`, `c`, `d`, `e`, `f`,`g`,`h`,`i`),
INDEX `idx1` (`b`,`c`)
)
PARTITION BY RANGE (`d`) (
PARTITION p1 VALUES LESS THAN (...)
...
PARTITION px VALUES LESS THAN (MAXVALUE)
)

But I am getting the exception A UNIQUE INDEX must include all columns in the table's partitioning function

I read through the documentation, and from what I can tell, I do have the correct setup. The partitioned column d is included in both the PRIMARY KEY and the UNIQUE KEY definition. What am I doing wrong here?

Upvotes: 1

Views: 1164

Answers (1)

Rick James
Rick James

Reputation: 142346

Change SERIAL to INT UNSIGNED AUTO_INCREMENT (or whatever is equivalent). The manual says:

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

Which means that there is an implicit

UNIQUE(i)

Which does not include d, the partition key.

CHARACTER SET latin1 is needed to avoid another error about index length. (8 columns is usually "too long" for an index.)

(And see my comments.)

Upvotes: 1

Related Questions